MySQL not using Index (despite FORCE INDEX)

Question

I need to do a live search using PHP and jQuery to select cities and countries from the two tables cities (almost 3M rows) and countries (few hundred rows).

For a short moment I was thinking of using a MyISAM table for cities as InnoDB does not support FULLTEXT search, however decided it is not a way to go (frequent table crashes, all other tables are InnoDB etc and with MySQL 5.6+ InnoDB also starts to support FULLTEXT index).

So, right now I still use MySQL 5.1, and as most cities consist of one-word only or max 2-3 Words, but e.g. "New York" - most people will not search for "York" if they mean "New York". So, I just put an index on the city_real column (which is a varchar).

The following query (I tried it in different versions, without any JOIN and without ORDER BY, with USE INDEX and even with FORCE INDEX, I have tried LIKE instead equal (=) but another post said = was faster and if the wildcard is only at the end, it is OK to use it), in EXPLAIN it always says "using where, using filesort". The average time for the query is about 4sec, which you have to admit is a little bit to slow for a live search (user typing in text-box and seeing suggestions of cities and countries)...

Live search (jQuery ajax) searches if the user typed at least 3 characters...

SELECT ci.id, ci.city_real, co.country_name FROM cities ci LEFT JOIN countries co ON(ci.country_id=co.country_id) WHERE city_real='cit%' ORDER BY population DESC LIMIT 5

There is a PRIMARY on ci.id and an INDEX on ci.city_real. Any ideas why MySQL does not use the index? Or how I could speed up the query? Or where else I should/should not set an INDEX?

Thank you very much in advance for your help!

Here's the explain output

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  ci  range   city_real   city_real   768 NULL    1250    Using where; Using filesort
1   SIMPLE  co  eq_ref  PRIMARY PRIMARY 6   fibsi_1.ci.country_id   1

Mark Byers · Accepted Answer · 2012-04-29 17:56:09Z

3

You should use WHERE city_real LIKE 'cit%', not WHERE city_real='cit%'.

I have tried LIKE instead equal (=) but another post said = was faster and if the wildcard is only at the end, it is OK to use it

This is wrong. = doesn't support wildcards so it will give you the wrong results.

Or how I could speed up the query?

Make sure you have an index on country_id in both tables. Post the output of EXPLAIN SELECT ... if you need further help.

edited Apr 29, 2012 at 17:56

answered Apr 29, 2012 at 17:47

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

Comments

Maxim Krizhanovsky · Accepted Answer · 2012-04-30 18:25:17Z

1

The query do use index as seen in the key field of explain output. The reason it uses filesort is the order by, and the reason it uses where is probably one of the fields (city_real, population) allows null values.

answered Apr 30, 2012 at 18:25

Maxim Krizhanovsky

26.8k5 gold badges62 silver badges91 bronze badges

Collectives™ on Stack Overflow

MySQL not using Index (despite FORCE INDEX)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related