MySQL Forums
Forum List  »  Optimizer & Parser

Re: Explain shows different results on two servers
Posted by: Rick James
Date: January 14, 2011 08:50AM

The likely answers:
* The constants in the queries are different
* The amount of data in the tables is different
* The cache on one machine happens to be 'better' populated at the moment
* Need for ANALYZE (which you are doing)
* You are using InnoDB, which collects 'statistics' in an inconsistent way
* etc, etc

I have seen as many as 6 different EXPLAIN plans on a single machine, taken consecutively. (This is best explained by the first and third bullet items.)

These is vital to digging further, please provide them:
* SHOW CREATE TABLE tbl\G -- engine, indexes (for each table)
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes (for each table)
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]

Ouch. I see you are doing a difficult problem -- latitude/longitude, and finding "nearest".

Options: ReplyQuote

Written By
Re: Explain shows different results on two servers
January 14, 2011 08:50AM

Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.