MySQL Query Too Slow For Limited Row

Question

I have a problem with mysql query. It is too slow about 101 seconds for limited 10 row. What could be the problem ?

Query is :

SELECT isteksikayet.BASVURU_NO AS BasvuruNo, isteksikayet.BASVURU_TARIHI AS BasvuruTarihi, mahalle.ad AS MahalleAdi, konular.ADI AS KonuAdi, 
sonucturleri.ADI AS Durum, isteksikayetdetay.GUNCELLEME_TARIHI AS BilgiTarihi, birimler.ad AS BirimAdi 

FROM isb_istek_sikayet isteksikayet
INNER JOIN tbl_sistem_mahalle mahalle
ON isteksikayet.MAHALLE_KODU = mahalle.kod 
INNER JOIN isb_konular konular
ON isteksikayet.KONU_KODU = konular.KODU
INNER JOIN isb_istek_sikayet_detay isteksikayetdetay
ON isteksikayet.BASVURU_NO = isteksikayetdetay.BASVURU_NO
INNER JOIN isb_sonuc_turleri sonucturleri
ON isteksikayetdetay.SONUC_KODU = sonucturleri.KODU
INNER JOIN mubim_birim birimler
ON isteksikayetdetay.DAIRE_KODU = birimler.kod
ORDER BY BasvuruNo DESC LIMIT 10;

Answer 1

It is true that the query returns only 10 row, but it has to order ALL the rows of a six tables join, this can really grow out of control quickly (like it already did on your database).

To avoid that I would suggest to order ONLY the table containing the BasvuruNocolumn and extracting the first 10 records in a subquery, and only then join with the rest of the tables. This way you should avoid ordering an overwhelming amount of records