Correct index for my MySQL query

Question

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

When I changed the query to:EXPLAIN SELECT f_shop, COUNT(sid), SUM(IF(toolbarUser=1,1,0)) FROM sal_forwarding GROUP BY f_shop. No Index is used too. I removed the DATE() function and no index is used. :( — user954740
– user954740, Commented Sep 27, 2011 at 11:08
@dnagirl, you have the correct answer, why did you delete it? — Johan
– Johan, Commented Sep 27, 2011 at 12:32
As a newbie, you may want to start accepting answers to your questions for the solutions found. This way, it gives credit to those helping, and others will continue to help in the future... look for the check mark to the left of the answers and check whichever was the solution. It also helps others from searching for older questions to still try and offer solutions when they are already long-ago solved. — DRapp
– DRapp, Commented Oct 10, 2011 at 12:40

Johan · Accepted Answer · 2011-09-27 13:08:31Z

2

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);
(a=1) => 1 if true, (a=1) => 0 if false, so the if(a=1,1,0) can be shortened;
It's a good idea to alias your aggregate columns, so you can refer to them by their alias later.
If you add the following index (and remove index forwardtime), you query will run even faster. KEY fasttime (forwardTime,f_shop,toolbarUser)
The previous point is especially true on InnoDB where MySQL will use a covering index if possible, which means that it will never read the table itself to retrieve the data if it can find all it needs in the index.

edited Sep 27, 2011 at 13:08

answered Sep 27, 2011 at 12:40

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ypercubeᵀᴹ Over a year ago

Perhaps you can add that a (f_shop, forwardTime) index will help.

ypercubeᵀᴹ Over a year ago

Or an (forwardTime) one, depending on distribution.

Johan Over a year ago

@ypercube, You are still accessing toolbaruser, so I doubt that will make much difference.

ypercubeᵀᴹ Over a year ago

That check is done only in the rows that pass the WHERE condition. The best way I think would be if forwardTime was a date column and an index of (forwardTime,f_shop,toolbarUser) was used.

Johan Over a year ago

@ypercube, you are correct, I'll add that to the answer thanks.

Collectives™ on Stack Overflow

Correct index for my MySQL query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related