MySQL Forums
Forum List  »  Optimizer & Parser

Explain shows different results on two servers
Posted by: Tomasz Wilk
Date: January 10, 2011 05:08PM

Hi,

I have a problem with a query that joins many tables. It loks like this:

select DISTINCT products.id as pid,
	localization.id as lid,localization.x as fx, 
	localization.y as fy,products.picture as img,
	products.price_before as pbefore, 
	products.price_after as pafter, prod_prom.date_stop as valid, 
	products_lang.name as fullname, products_lang.short_name as shortname, 
	products_lang.description as pdescription, firms.name as fname, 
	address.code as fcode, address.city as fcity, address.street as fstreet, 
	address.number as fnumber, address.flat as fflat, 
	localization.tel as ftel, localization.fax as ffax, 
	localization.www as fwww, localization.email as femail , 
	POW( localization.x - 637340, 2 ) + POW( localization.y -486775, 2 ) AS distsq 
FROM products 
JOIN products_lang ON products_lang.product_id = products.id 
JOIN products_localizations_xref ON products_localizations_xref.product_id = products.id 
JOIN localization ON products_localizations_xref.localization_id = localization.id 
JOIN promotions AS loc_prom ON (localization.id = loc_prom.sub_id AND loc_prom.type = 4) 
JOIN promotions AS prod_prom ON (products.id = prod_prom.sub_id AND prod_prom.type = 0) 
JOIN firms ON firms.auth_id = localization.auth_id 
JOIN address ON localization.address = address.id 
JOIN products_categories_xref_search ON products_categories_xref_search.product_id = products.id

WHERE loc_prom.date_start <= CURDATE( ) 
AND loc_prom.date_stop >= CURDATE( ) 
AND prod_prom.date_start <= CURDATE( ) 
AND prod_prom.date_stop >= CURDATE( ) 
AND x > 587340 AND x <687340 AND y > 436775 AND y <536775 
AND products_lang.lang = 'pl' 
AND products_categories_xref_search.category_id = '2357' 
ORDER BY distsq,fullname LIMIT 0,10
The same set of data (tables droped and recreated) is on the development machine and on the production server. On both machines mysql is in version 5.1 (development is linux-based and production is Windows Server). On my development machine the above query runs in nice miliseconds. On the production server though it takes under 4 seconds for the same data. When I do EXPLAIN I get different results as well. For the fast machine:
+----+-------------+---------------------------------+--------+-----------------------+---------+---------+-------------------------------------------------------+------+---------------------------------+
| id | select_type | table                           | type   | possible_keys         | key     | key_len | ref                                                   | rows | Extra                           |
+----+-------------+---------------------------------+--------+-----------------------+---------+---------+-------------------------------------------------------+------+---------------------------------+
|  1 | SIMPLE      | products                        | ALL    | PRIMARY               | NULL    | NULL    | NULL                                                  |   59 | Using temporary; Using filesort |
|  1 | SIMPLE      | prod_prom                       | ref    | time,type             | type    | 102     | const,promocje.products.id                            |    1 | Using where                     |
|  1 | SIMPLE      | products_lang                   | eq_ref | PRIMARY               | PRIMARY | 106     | promocje.prod_prom.sub_id,const                       |    1 | Using where                     |
|  1 | SIMPLE      | products_localizations_xref     | ref    | PRIMARY               | PRIMARY | 98      | promocje.products_lang.product_id                     |   14 | Using where; Using index        |
|  1 | SIMPLE      | products_categories_xref_search | eq_ref | PRIMARY               | PRIMARY | 102     | promocje.products_localizations_xref.product_id,const |    1 | Using where; Using index        |
|  1 | SIMPLE      | localization                    | eq_ref | PRIMARY,point,auth_id | PRIMARY | 98      | promocje.products_localizations_xref.localization_id  |    1 | Using where                     |
|  1 | SIMPLE      | firms                           | eq_ref | PRIMARY               | PRIMARY | 98      | promocje.localization.auth_id                         |    1 |                                 |
|  1 | SIMPLE      | loc_prom                        | ref    | time,type             | type    | 102     | const,promocje.localization.id                        |    1 | Using where                     |
|  1 | SIMPLE      | address                         | eq_ref | id                    | id      | 98      | promocje.localization.address                         |    1 |                                 |
+----+-------------+---------------------------------+--------+-----------------------+---------+---------+-------------------------------------------------------+------+---------------------------------+
And for the slow machine:

+----+-------------+---------------------------------+--------+--------------+---------+---------+-------------------------------------------------------+------+----------------------------------------------+
| id | select_type | table                           | type   | possible_keys |key     | key_len | ref                                                   | rows | Extra                                        |
+----+-------------+---------------------------------+--------+--------------+---------+---------+-------------------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | loc_prom                        | ALL    | NULL          | NULL    | NULL    | NULL                                                  | 7811 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | prod_prom                       | ALL    | NULL          | NULL    | NULL    | NULL                                                  | 7811 | Using where; Using join buffer               |
|  1 | SIMPLE      | products_localizations_xref     | eq_ref | PRIMARY       | PRIMARY | 196     | promocje.prod_prom.sub_id,promocje.loc_prom.sub_id    |    1 | Using index                                  |
|  1 | SIMPLE      | products_categories_xref_search | eq_ref | PRIMARY       | PRIMARY | 102     | promocje.prod_prom.sub_id,const                       |    1 | Using index                                  |
|  1 | SIMPLE      | localization                    | eq_ref | PRIMARY       | PRIMARY | 98      | promocje.loc_prom.sub_id                              |    1 | Using where                                  |
|  1 | SIMPLE      | firms                           | eq_ref | PRIMARY       | PRIMARY | 98      | promocje.localization.auth_id                         |    1  |                                              |
|  1 | SIMPLE      | products                        | eq_ref | PRIMARY       | PRIMARY | 98      | promocje.products_categories_xref_search.product_id   |    1| Using where                                  |
|  1 | SIMPLE      | products_lang                   | eq_ref | PRIMARY       | PRIMARY | 106     | promocje.products_localizations_xref.product_id,const |    1 | Using where                                  |
|  1 | SIMPLE      | address                         | eq_ref | id            | id      | 98      | promocje.localization.address                         |    1 |                                              |
+----+-------------+---------------------------------+--------+---------------+---------+---------+-------------------------------------------------------+------+----------------------------------------------+

The slow server optimizes the query differently (with analyzing over 7000 rows, whereas fast server analyzes at most 60 (those are the values from the column "rows" in explain). My question is why the optimization is going differently on those machines and how to force the quick way on the slow server?
To give you more information, mysqlcheck with analyzing tables (and a bunch of other options) is run periodically on both machines. Please help.

Options: ReplyQuote


Subject
Views
Written By
Posted
Explain shows different results on two servers
4175
January 10, 2011 05:08PM


Sorry, you can't reply to this topic. It has been closed.

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.