Reputation: 151
I tried to make indexes on my table. But First mysql uses it then that doesn't use the index.
It is my table indexes. enter image description here
My Query.
EXPLAIN SELECT
COUNT(DISTINCT K.bayiid) AS toplam,
SUM(K.tutar) AS yatirilan,
SUM(IF(K.durum='2', K.tutar*K.toplam,0)) AS kazanc,
SUM(IF(K.durum='-1', K.tutar, 0)) AS kayip,
SUM(IF(K.durum='1', K.tutar,0)) AS devam,
SUM(IF(K.durum='0', K.tutar,0)) AS iptal,
SUM(1) AS oynanan,
SUM(IF(K.durum='2', 1,0)) AS kazanan,
SUM(IF(K.durum='-1', 1,0)) AS kaybeden,
SUM(IF(K.durum='1', 1,0)) AS devameden,
SUM(IF(K.durum='0', 1,0)) AS iptalolan,
U.*
FROM kuponlar AS K
INNER JOIN users AS U ON U.id = K.bayiid AND U.durum != '4' AND U.id = '26689'
WHERE K.durum < 3 AND K.tarih >= '2016-12-01 00:00:00' AND K.tarih <= '2016-12-31 23:59:59'
Query explaination enter image description here
But i don't want to use USE INDEX or FORCE INDEX. Do you have any idea why mysql works unstable?
Upvotes: 0
Views: 206
Reputation: 142528
ON U.id = K.bayiid
AND U.durum != '4'
AND U.id = '26689'
For clarity, move the last 2 parts to WHERE
; leave only the condition(s) that defines the JOIN
.
WHERE K.durum < 3
AND K.tarih >= '2016-12-01 00:00:00'
AND K.tarih <= '2016-12-31 23:59:59'
Suggestion: For date ranges, do something like this:
AND K.tarih >= '2016-12-01'
AND K.tarih < '2016-12-01' + INTERVAL 1 MONTH
For K
, have
INDEX(bayiid, darum)
INDEX(bayiid, tarih)
(Without knowing the distribution of the data, I cannot predict which is better. The Optimizer will choose.)
If you want to discuss this further, please provide SHOW CREATE TABLE
for each table. If there happen to be datatype inconsistencies, that could cause a lot of trouble, and we cannot see it without the SHOW
.
Upvotes: 2