About mysql query with inner Join content

zujrkrfu 于 2022-12-22 发布在 Mysql

关注(0)|答案(1)|浏览(151)

I'm a beginner in php and I want to ask you if the query and table schema I have set up is the right way for performance. Note: If you want me to follow a different way, please provide sample for me, thanks

$digerilanlar = DB::get('
SELECT Count(siparisler.hid) AS siparissayisi, 
siparisler.hid, ilanlar.id, ilanlar.seflink, ilanlar.kategori, ilanlar.baslik, 
ilanlar.yayin, ilanlar.tutar, ilanlar.sure, ilanlar.onecikan, ilanlar.guncellemetarihi,
uyeler.nick, uyeler.foto, uyeler.online, uyeler.ban FROM ilanlar 
inner join uyeler ON uyeler.id=ilanlar.ilansahibi 
LEFT JOIN siparisler ON ilanlar.id = siparisler.hid
WHERE ilanlar.kategori= '.$kat->id.' and ilanlar.yayin=1 and uyeler.ban=0 
GROUP BY ilanlar.id
ORDER BY guncellemetarihi DESC 
LIMIT 0,12');

DATABASE DESİGN

Table engine MyISAM MYSQL versiyon 5.7.14

TABLE:İLANLAR
ilansahibi (int)= index
kategori (int)= index
yayin (int)= index

TABLE:UYELER
ban (int)= index

TABLE:SİPARİSLER
hid (int)= index

mysql

来源：https://stackoverflow.com/questions/74822253/about-mysql-query-with-inner-join-content

1条答案

按热度按时间

nx7onnlm1#

This will probably require two temp tables and two sorts:

GROUP BY  ilanlar.id
    ORDER BY  guncellemetarihi DESC

Assuming that guncellemetarihi is update_date , this is not identical, but probably gives you what you want, but with only one temp table and sort:

GROUP BY  guncellemetarihi,      id
    ORDER BY  guncellemetarihi DESC, id DESC

COUNT(x) checks x for being NOT NULL . If that is not necessary, simply do COUNT(*) .

SELECT COUNT(hid), hid

does not make sense. The COUNT implies that there may be multiple "hids", but hid implies that there is only one. (Since I don't understand to objective, I cannot advise which direction to change things.)
This composite INDEX may help:

ilanlar:  INDEX(kategori, yayin, ilansahibi, id)

You should switch from ENGINE=MyISAM to ENGINE=InnoDB.
More on making indexes: Index Cookbook
To discuss further, please provide SHOW CREATE TABLE and EXPLAIN SELECT ...

赞(0）回复(0）举报 2022-12-22

我来回答

About mysql query with inner Join content

1条答案

相关问题

热门标签

最新问答