Question regarding mysql index

Hello,

I have a table with the following structure:

id (int, auto increment), text1, lang

I also have created an index on text1,lang.

When I run the following query:

select * from mytable where name like 'cre%' I get 2500 records back.

and when I run

select * from mytable where name like 'cre%' and lang='en' I get 25 records back.

However if I run:
explain select * from mytable where name like 'cre%' and lang='en' I see that 2500 records are scanned.

Is that correct, since I have created the index on those two columns? My assumption was that only 25 records should be scanned.

Thanks a lot!
infodiggerAsked:
Who is Participating?
 
regmigrantConnect With a Mentor Commented:
Are there separate indexes on Text1 and lang or a single multicolumn index?

 As I understand it a multi-column index will only be used if the where clause is against the left most column of the index - in your example the Lang part is not available to the optimizer if its the second column of the index - you may get a better result with separate indices on each.

This link shows some useful hints :
https://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
0
 
Chris StanyonCommented:
What output do you get from your EXPLAIN statement
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.