About mysql query with inner Join content-CodePudding

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

enter image description here

CodePudding user response：

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 ...