Slow mysql query when using where clause

Question

I've taken a zip code database and pre-calculated the distances to other zip codes within a certain radius. The database itself is about 2.5GB so not anything extraordinary.

The goal of doing this is to be able to:

select * from zipcode_distances where zipcode_from=92101 and distance < 10;

So far the only index i've defined is:

(zipcode_from, distance)

However, running the query takes about 20 seconds to get the results.

When I remove the "and distance < 10" clause, the results are instantaneous.

Any advice would be appreciated.

Edit:

Here is the create statement:

delimiter $$

CREATE TABLE `zipcode_distances` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `zipcode_from` char(5) COLLATE utf8_bin NOT NULL,
  `zipcode_to` char(5) COLLATE utf8_bin NOT NULL,
  `distance` double unsigned NOT NULL COMMENT 'stored in miles',
  PRIMARY KEY (`id`),
  KEY `idx_zip_from_distance` (`zipcode_from`,`distance`)
) ENGINE=MyISAM AUTO_INCREMENT=62548721 DEFAULT CHARSET=utf8 COLLATE=utf8_bin$$

Here is the explain:

explain extended select * from zipcode_distances where zipcode_from=90210 and distance < 10;

Results:

id, select_type, table, possible_keys, key, key_len, ref, rows, filtered, Extra 1, SIMPLE, zipcode_distances, ALL, idx_zip_from_distance, null, null, null, 62548720, 100.00, Using where

Thank you!

Please show the table layout because at this time it is hard to help unless we see how your store the data for distances to zip code. — donlaur
– donlaur, Commented Dec 30, 2012 at 18:11
Did you run both queries multiple times? The first time you run, caches are filled. Subsequent runs should be faster. You may simply have preloaded the cache with the first query, and then used it for the second one. — Gordon Linoff
– Gordon Linoff, Commented Dec 30, 2012 at 18:26

Gordon Linoff · Accepted Answer · 2012-12-30 18:31:08Z

3

I see no problem with MySQL using the index for the query. I do wonder if the type conversion from 92101 could be confusing it.

Do you get the same poor performance with this?

select * from zipcode_distances where zipcode_from='92101' and distance < 10;

The other issue is how you are doing the timings. You have to run multiple times to avoid the effects of filling the cache.

answered Dec 30, 2012 at 18:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

john Over a year ago

this is correct; the type conversion was the culprit! thank you!!

Xnoise Over a year ago

Just a hint: please when you do a explain extended, paste as well the warnings. Usually in the warning, you will have the aproximate query that will get executed, along with a conversion function for your case. These types of errors are very hard to track initially, because all seems normal. One thing that should raise questions is the fact that it was a complete table scan (one of the worse types of scans that can exist). Next you need to enable warnings, and fix the conversion issue.

Collectives™ on Stack Overflow

Slow mysql query when using where clause

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related