

| 1 | SIMPLE | products | ALL | PRIMARY | NULL | NULL | NULL | 59 | Using temporary Using filesort | | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | When I do EXPLAIN I get different results as well.

On the production server though it takes under 4 seconds for the same data. On my development machine the above query runs in nice miliseconds.
Mysql optimizer different results between servers windows#
On both machines mysql is in version 5.1 (development is linux-based and production is Windows Server). The same set of data (tables droped and recreated) is on the development machine and on the production server. JOIN products_categories_xref_search ON products_categories_xref_search.product_id = products.idĪND x > 587340 AND x 436775 AND y <536775ĪND products_categories_xref_search.category_id = '2357' JOIN address ON localization.address = address.id JOIN promotions AS prod_prom ON (products.id = prod_prom.sub_id AND prod_prom.type = 0) JOIN promotions AS loc_prom ON (localization.id = loc_prom.sub_id AND loc_prom.type = 4) JOIN localization ON products_localizations_xref.localization_id = localization.id JOIN products_localizations_xref ON products_localizations_xref.product_id = products.id

JOIN products_lang ON products_lang.product_id = products.id Localization.www as fwww, localization.email as femail , Localization.tel as ftel, localization.fax as ffax, Products_scription as pdescription, firms.name as fname,Īde as fcode, address.city as fcity, address.street as fstreet,Īddress.number as fnumber, address.flat as fflat, Products_lang.name as fullname, products_lang.short_name as shortname, Products.price_after as pafter, prod_prom.date_stop as valid, Localization.y as fy,products.picture as img, Localization.id as lid,localization.x as fx, I have a problem with a query that joins many tables.
