Menu
I am facing a strange performance issue with a mysql query.
SELECT `pricemaster_products`.*, `products`.* FROM `pricemaster_products` LEFT JOIN `products` ON `pricemaster_products`.`ean` = `products`.`products_ean` I explicitely want to use a left join. But the query takes a lot more time then it should.
I tried to change the join to an INNER JOIN. The query now is really fast, but the result is not what I need.
I used explain and came to the following conclusion:
If I use a "LEFT JOIN" then an EXPLAIN of the query results in...
type: "ALL" possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 90.000 / 50.000 (the full number of the corresponding table) ... for both tables.
If I use an "INNER JOIN" then EXPLAIN gives:
For table "products":
Same result as above. For table "pricemaster_products":
type: "ref" possible_keys: "ean" key: ean key_len: 767 ref: func rows: 1 extra: using where Both tables have indexes set on the relevant columns. The only possible reason I could think of for the LEFT JOIN to be so slow is that is does not use the index at all. But why would it not?
The table structure is as follows:
CREATE TABLE IF NOT EXISTS `pricemaster_products` ( `id` int(11) NOT NULL AUTO_INCREMENT, `provider` varchar(255) CHARACTER SET utf8 NOT NULL, `ean` varchar(255) CHARACTER SET utf8 NOT NULL, `title` varchar(255) CHARACTER SET utf8 NOT NULL, `gnp` double DEFAULT NULL, `vat` int(11) DEFAULT NULL, `cheapest_price_with_shipping` double DEFAULT NULL, `last_cheapest_price_update` int(11) DEFAULT NULL, `active` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `ean` (`ean`), KEY `title` (`title`), KEY `gnp` (`gnp`), KEY `vat` (`vat`), KEY `provider` (`provider`), KEY `cheapest_price_with_shipping` (`cheapest_price_with_shipping`), KEY `last_cheapest_price_update` (`last_cheapest_price_update`), KEY `active` (`active`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=58436 ; CREATE TABLE IF NOT EXISTS `products` ( `products_id` int(11) NOT NULL AUTO_INCREMENT, `products_ean` varchar(128) DEFAULT NULL, `products_status` tinyint(1) NOT NULL DEFAULT '1', [a lot more of fields with no connection to the query in question] PRIMARY KEY (`products_id`), KEY `products_status` (`products_status`), KEY `products_ean` (`products_ean`), ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=105518 ; 
766
The Benefits and Drawbacks of Using Indexes in MySQLIndexes consume disk space. Indexes degrade the performance of INSERT, UPDATE and DELETE queries – when data is updated, the index needs to be updated together with it. MySQL does not protect you from using multiple types of indexes at the same time.
A legitimately needed left join was present in the view and any attempt to create a index raised a "not allowed construct" error. After hours of trial and error I managed to do it work. It's not at all an elegant approach, but it works for outer joins.
Indexes can help improve the performance of a nested-loop join in several ways. The biggest benefit often comes when you have a clustered index on the joining column in one of the tables. The presence of a clustered index on a join column frequently determines which table SQL Server chooses as the inner table.
You'll use INNER JOIN when you want to return only records having pair on both sides, and you'll use LEFT JOIN when you need all records from the “left” table, no matter if they have pair in the “right” table or not.
The two relevant fields for the join did not have exactly the same type (varchar(255) with CHARACTER SET utf8 and varchar(128) with latin1). I did set both to the same length and character set, and now the query with the LEFT JOIN works as expected.
165
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
