What is wrong with my index on this MySQL query?

Question

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
                inner join xml_record_supplier on xml_record_product.product_id = xml_record_supplier.product_id AND supplier_price > 0
                where contributor_title like '%josh%'
                order by xml_record_product.product_publication_date DESC limit 20;

Gives:

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

But if I take out the inner join on xml_record_supplier, I get this:

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

I need to know why this happens, and how it can be prevented? As far as I understood, EXPLAIN would show the tables read sequentially from the inner join. Which is not happening, and I've made sure I have the index set on product_id on these queries (for xml_record_contributor it's a mix of product_id and contributor_id)

On the xml_record_supplier I have product_id and supplier_price in the index (however, i did try many combinations as well, think ALL of them)

Any ideas what I can try? I need to limit the selection to suppliers that has a price of higher than 0 and with the addition of that inner join it gets out of hand quickly.

Thanks for any input!

/edit- here's the describes on all the tables

xml_record_product (index is product_id,product_publication_date desc)

product_id  bigint(20)  NO  PRI     auto_increment
    product_isbn13  bigint(13)  NO          
    product_form    varchar(255)    NO          
    product_num_pages   int(11) NO          
    product_BASICMainSubject    varchar(255)    NO          
    product_BICMainSubject  varchar(255)    NO          
    product_audience_code   int(11) NO          
    product_country_of_publication  varchar(255)    NO          
    product_publishing_status   int(11) NO          
    product_publication_date    varchar(255)    NO  MUL     
    product_short_description   varchar(350)    NO          
    product_description text    NO          
    product_imprint varchar(255)    NO          
    product_image   varchar(255)    NO          
    product_publisher_name  varchar(255)    NO          
    product_title   varchar(255)    NO          
    product_active  int(11) NO      1   
    cat_id  int(11) NO          
    product_isFeatured  int(11) NO          
    product_isNewArival int(11) NO  

xml_record_contributor (index is product_id,contributor_id)

contributor_id  int(11) NO  PRI     auto_increment
product_id  bigint(20)  NO  MUL     
contributor_title   varchar(255)    NO          

xml_record_supplier (index is product_id,supplier_id,supplier_price)

supplier_id int(11) NO  PRI     auto_increment
product_id  bigint(20)  NO  MUL     
supplier_name   varchar(255)    NO          
supplier_product_availability   int(11) NO          
supplier_price  varchar(255)    NO          
supplier_currency_code  varchar(255)    NO          
supplier_supply_to  varchar(255)    NO          
supplier_price_status   varchar(255)    NO          
Description text    NO          
URL varchar(255)    NO          
Image1  varchar(255)    NO          
Image1_sml  varchar(255)    NO          
Image1Alt   varchar(255)    NO          

xml_record_subject (index is subject_id,product_id)

subject_id  int(11) NO  PRI     auto_increment
product_id  bigint(20)  NO  MUL     
subject_heading_text    varchar(255)    YES MUL     
ParentID    varchar(20) NO  

EDIT

Here is the SHOW INDEX FROM for xml_record_supplier

xml_record_supplier 1   sup_product_id  1   product_id  A   447712              BTREE
xml_record_supplier 1   sup_product_id  2   supplier_id A   895424              BTREE
xml_record_supplier 1   sup_product_id  3   supplier_price  A   895424              BTREE

Answer 1

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, rows in the table and cardinality, getting rid of the filesort may be quicker. Try using FORCE INDEX (composite_index_on_title_and_date) to get rid of the filesort and compare results.