Help me understand why this index won't work (MySQL)

[guest2013-1]

Here is my select statement:

select
                xml_record_product.product_id, 
                IfNull(xml_record_product.product_short_description,xml_record_product.product_description) AS BookDescription, 
                xml_record_product.product_image, 
                xml_record_product.product_publisher_name AS Publisher, 
                xml_record_product.product_title AS BookTitle, 
                xml_record_product.product_form, 
                xml_record_product.product_num_pages, 
                xml_record_product.product_BASICMainSubject, 
                xml_record_product.product_BICMainSubject, 
                xml_record_product.product_audience_code, 
                xml_record_product.product_country_of_publication, 
                xml_record_product.product_publishing_status, 
                xml_record_product.product_publication_date AS BookDate, 
                xml_record_product.product_imprint, 
                xml_record_product.product_active, 
                xml_record_product.product_isFeatured, 
                xml_record_product.product_isNewArival, 
                xml_record_product.product_short_description, 
                xml_record_product.product_description, 
                xml_record_product.product_isbn13 AS ISBN,
                xml_record_subject.subject_heading_text
                FROM xml_record_product 
                inner join xml_record_contributor ON xml_record_product.product_id = xml_record_contributor.product_id
                inner join xml_record_subject on xml_record_contributor.product_id = xml_record_subject.product_id
                [color=red]inner join xml_record_supplier on xml_record_product.product_id = xml_record_supplier.product_id AND supplier_price > 0[/color]
                where contributor_title like '%josh%'
                order by xml_record_product.product_publication_date DESC limit 20;

If I take out the red part, this is my EXPLAIN:

SIMPLE	xml_record_product	index	PRIMARY	pub_date	265		20	

SIMPLE	xml_record_subject	ref	product_id_sub	product_id_sub	8	mysupplier1.xml_record_product.product_id	1	

SIMPLE	xml_record_contributor	ref	cont_product_id	cont_product_id	8	mysupplier1.xml_record_subject.product_id	1	Using where

WITH the red part, it gives me this:

SIMPLE	xml_record_supplier	index	sup_product_id	sup_product_id	265		895424	Using where; Using index; Using temporary; Using filesort

SIMPLE	xml_record_subject	ref	product_id_sub	product_id_sub	8	mysupplier1.xml_record_supplier.product_id	1	

SIMPLE	xml_record_product	eq_ref	PRIMARY	PRIMARY	8	mysupplier1.xml_record_supplier.product_id	

SIMPLE	xml_record_contributor	ref	cont_product_id	cont_product_id	8	mysupplier1.xml_record_subject.product_id	1	Using where

Now I've tried all combinations of index's on the xml_record_supplier table. I just can't seem to figure out why this screws up so badly. Any ideas?

 

[guest2013-1]

Oh and FYI, I need the xml_record_supplier to be in there to limit the pricing to not be 0, otherwise I risk pulling 20 records, half of which do not have pricing...

 

[guest2013-1]

http://stackoverflow.com/questions/3335266/what-is-wrong-with-my-index-on-this-mysql-query

Reference to the same question, hopefully get an answer there

 

[hof]

Can you post a DESCRIBE table so we can see your indexes and table structure?

I always keep my joins as simple as possible. Move supplier_price > 0 to the WHERE condition. I'm guessing because of that condition that MySQL's trying to use an index in xml_record_supplier. As a result it can't use an index on product_publication_date which means it's having to sort without an index. If that's the case, there isn't an easy solution as you can't build a composite index with fields from different tables.

MySQL's planner prefers to wittle down the number of rows than optimize for an ORDER BY. Depending on the number of results and rows in the table, getting rid of the filesort may be quicker. Try using FORCE INDEX (yourindex) to get rid of the filesort and compare results.

 

[davemc]

AND.

Try brackets.

 

[guest2013-1]

Posted the describe a bit back on stackoverflow. no difference between having the price > 0 in where or the inner join and (nor do brackets work)

any ideas tho? the same indexes appear to work for contributor and subject?