StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat is wrong with my index on this MySQL query?
text
Body
copied!<pre><code>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; </code></pre> <p>Gives:</p> <pre><code>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 </code></pre> <p>But if I take out the inner join on xml_record_supplier, I get this:</p> <pre><code>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 </code></pre> <p>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)</p> <p>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)</p> <p>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.</p> <p>Thanks for any input!</p> <p>/edit- here's the describes on all the tables</p> <pre><code>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 </code></pre> <p>xml_record_contributor (index is product_id,contributor_id)</p> <pre><code>contributor_id int(11) NO PRI auto_increment product_id bigint(20) NO MUL contributor_title varchar(255) NO </code></pre> <p>xml_record_supplier (index is product_id,supplier_id,supplier_price)</p> <pre><code>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 </code></pre> <p>xml_record_subject (index is subject_id,product_id)</p> <pre><code>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 </code></pre> <p><strong>EDIT</strong></p> <p>Here is the SHOW INDEX FROM for xml_record_supplier</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload