CODEWITHSUNDEEP
phpmysqlsql
Ece
Ece

Reputation: 148

Which columns to index in MySQL?

SELECT ID,uye,kazanilacakmiktar FROM kupon WHERE durum=0 AND ID IN (
select c.kupon
from kuponbahis c join
     bahis b 
     on ((b.sonuc = c.secim) OR (b.sonuc=5)) and b.ID = c.bahis 
group by c.kupon
having sum(c.bahis = '999') > 0 and
       count(*) = (SELECT COUNT(*) FROM kuponbahis WHERE kupon = c.kupon));

I have a query like this. I have indexes at kupon.ID, kupon.uye, kupon.kazanilacakmiktar, kuponbahis.bahis, kuponbahis.secim but it still has a significant runtime.

What I'm missing, what I'm doing wrong?

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The obvious indexes for the query are:

  • kupon(durum, id)
  • kuponbahis(kupon)
  • bahis(id, sonuc)

However, I'm not convinced that these indexes will be a big help for t his query The query is rather complicated and there may be alternative ways of expressing the logic.

Upvotes: 2

Related Questions