I have the following Table:
CREATE TABLE `sal_forwarding` (
`sid` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`f_shop` INT(11) NOT NULL,
`f_offer` INT(11) DEFAULT NULL,
.
.
.
PRIMARY KEY (`sid`),
KEY `forwardTime` (`forwardTime`,`f_shop`),
KEY `forwardTime_2` (`forwardTime`),
KEY `f_shop` (`f_shop`)
) ENGINE=INNODB AUTO_INCREMENT=10457068 DEFAULT CHARSET=latin1
This table has more than 5 million rows.
I've set indexes, as you can see above, but in my query no indexes are used and I don't understand why. Does anybody see my problem?
Explain:
EXPLAIN SELECT
f_shop
, COUNT(sid)
, SUM(IF(toolbarUser=1,1,0))
FROM sal_forwarding
WHERE DATE(forwardTime) = "2011-09-01"
GROUP BY f_shop
Result:
+----+-------------+----------------+-------+---------------+--------+---------+--------+--------+-------------+
| ID | SELECT_TYPE | TABLE | TYPE | POSSIBLE_KEYS | KEY | KEY_LEN | REF | ROWS | EXTRA |
+----+-------------+----------------+-------+---------------+--------+---------+--------+--------+-------------+
| | | | | | | | | | |
| 1 | SIMPLE | sal_forwarding | index | (NULL) | f_shop | 4 | (NULL) | 232449 | Using where |
+----+-------------+----------------+-------+---------------+--------+---------+--------+--------+-------------+
MySQL cannot use an index on a column inside a function.
Remove the function date()
from your select and MySQL will use the index.
You can do this by changing your column definition of forwardtime
to DATE
Or you can change the query like so
SELECT
f_shop
, COUNT(*) as RowCount
, SUM(toolbarUser=1) as NumberOfToolbarUsers
FROM sal_forwarding
WHERE forwardTime BETWEEN '2011-09-01 00:00' AND '2011-09-01 23:59'
GROUP BY f_shop
Remarks
count(*)
is faster than count(namedcolumn)
; if(a=1,1,0)
can be shortened; forwardtime
), you query will run even faster. KEY fasttime (forwardTime,f_shop,toolbarUser)