creating mysql index

Question

I have query:

EXPLAIN SELECT * FROM _mod_news USE INDEX ( ind1 )  WHERE show_lv =1 AND active =1 AND START <= NOW( ) 
AND ( END >= NOW( )  OR END =  "0000-00-00 00:00:00" ) AND id <>  "18041" AND category_id =  "3" AND leta =1 ORDER BY sort_id ASC , DATE DESC  LIMIT 7

result:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  _mod_news   ref ind1    ind1    2   const,const 11386   Using where; Using filesort

mysql is performing full table scan

ind1 =

ALTER TABLE `_mod_news` ADD INDEX ind1 ( `show_lv`, `active`,  `start`, `end`, `id`, `category_id`, `leta`, `sort_id`, `date`);

I tested on following index, but nothing changes

ALTER TABLE `_mod_news` ADD INDEX ind1 ( `show_lv`, `active`,  `start`, `end`, `id`, `category_id`, `leta`);

Question is: where i can learn how to create indexes on many where conditions? Or someone can explain how to tell to mysql to use and index and not to scan whole table. Thanks.

All columns in your WHERE clause need to be on the index for an index to be used. — Kermit
– Kermit, Commented Sep 10, 2012 at 15:44
You may need to use tick marks around reserved words such as END. — Kermit
– Kermit, Commented Sep 10, 2012 at 15:49

Imre L · Accepted Answer · 2012-09-10 19:17:37Z

1

I would suggest not forcing index. Mysql is a great at selecting the best possible index unless you have better understanding of the data you are querying.

You cannot use ORDER BY optimization because you are mixing the ASC and DESC in that part.

Therefore your only option is to create index such that:

constant values before range
integers before dates, dates before strings, smaller size vales before bigger size values

Creating a large index also adds an overhead to storage and insert-update time, so i would not add to index fields that are not eliminating a lot of rows (i.e 90% or rows have a value of 1 or i.e id<>"18041" but that most likely eliminates < 1% of rows).

If you want to learn more about optimizing: http://dev.mysql.com/doc/refman/5.0/en/select-optimization.html

Create multiple different indexes (on decent size of data you expect seeing in the table), see which one mysql chooses, benchmark them by forcing each one of them, then use your common sense to cut down on index space usage.

You can see from you EXPLAIN output that it is actually NOT performing a full table scan because in that case it would not display it using the index even when you are forcing it.

edited Sep 10, 2012 at 19:17

answered Sep 10, 2012 at 19:10

Imre L

6,29126 silver badges32 bronze badges

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

1 Comment

Guntis Over a year ago

Thanks. But rows = 11386, that means that mysql is operating with 11386 rows?

Aleksandar Vucetic · Accepted Answer · 2012-09-10 15:38:42Z

0

You can try with USE INDEX or FORCE INDEX

answered Sep 10, 2012 at 15:38

Aleksandar Vucetic

15k9 gold badges56 silver badges56 bronze badges

1 Comment

Guntis Over a year ago

i already have use index and explain is showing that ind1 is used.

Collectives™ on Stack Overflow

creating mysql index

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related