Slow Query + Indexing Issue

[dawieharmse]

Hi All,

 

I hope someone can help me here, I dont use forums too often, so I will try to be as descriptive as possible.

 

Ok, here is the problem:

I have a database, which has about 2 million records, and obviously with huge databases you get slow execution of queries. I have assigned indexes to all the required fields yet the fastest I can get the query to execute is about 50 seconds.

 

I have attached and the query explained so you can see which indexes it uses and which it does not.

 

Also, please see a common query below:

Common Query: (This obviously changes, depending on the Search you do)

SELECT Min(xml_record_supplier.supplier_price) AS LowestSupplierPrice,

xml_record_supplier.supplier_currency_code,

Group_Concat(distinct xml_record_contributor.contributor_title) AS Authors,

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_id,

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_supplier.supplier_product_availability AS Stock,

xml_record_subject.subject_heading_text As CatName,

xml_record_measurement.mes_weight As BookWeight

FROM xml_record_product USE INDEX(product_id)

Inner Join xml_record_contributor USE INDEX(cont_product_id) ON xml_record_product.product_id = xml_record_contributor.product_id

Inner Join xml_record_subject USE INDEX(sub_product_id) ON xml_record_product.product_id = xml_record_subject.product_id

Inner Join xml_record_supplier USE INDEX(supp_product_id) ON xml_record_supplier.product_id = xml_record_product.product_id

Inner Join xml_record_measurement USE INDEX(mes_product_id) ON xml_record_product.product_id = xml_record_measurement.product_id

WHERE xml_record_supplier.supplier_price <> 0 AND

xml_record_supplier.product_id = xml_record_product.product_id AND

xml_record_contributor.contributor_title LIKE '%John%'

GROUP BY xml_record_product.product_id

ORDER BY xml_record_product.product_publication_date DESC LIMIT 0, 10

 

Please help me! I am getting desperate!

 

The Site runs on the following Dedicated Server:

Windows Server 2008 R2

Intel Xeon E5504 @ 2.00 GHz

6 Gig RAM

 

MySQL Version: MySQL 5.1.46 Community

 

Kind Regards,

Dawie

 

 

[attachment deleted by admin]

[Mchl]

The biggest problem seems to be with you xml_record_supplier table.

I suggest dropping USE INDEX(supp_product_id) (as MySQL is not using it anyway) and instead create index on columns (product_id, supplier_price)

 

How many rows in this table have supplier_price <> 0 ?

[dawieharmse]

Thanks Mchl for your quick response,

 

I have indexes on both the product_id and supplier_price.

 

xml_record_supplier has about 580 000 records, with 500 000 records with empty values.

 

[Mchl]

But do you have an index, that covers both columns?

http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

 

Also, you say 'empty values'. Are these 'empty' values equal to 0 or NULL?

[dawieharmse]

I have an index for each column. One for product_id and one for supplier_price.

 

Someone also suggested that I use FULLTEXT indexes and using MATCH().....AGAINST() in my Query instead of LIKE. So implementing now to see what it will do.

 

 

[Mchl]

I am suggesting you try one index created on BOTH columns (not two separate indexes). I can't see how FULL TEXT search would help here.

[dawieharmse]

ok great. thanks, will try doing that as well.

[dawieharmse]

Hi Mchl,

 

I CANNOT BELIEVE IT! Its working Perfectly.

 

See attached screenshot of the EXPLAIN now.

 

And the Query executes in 12 Seconds, compared to the 50 to 70 seconds it used to be.

 

Thanks a lot for all your help & advise!!!!

 

[attachment deleted by admin]

[Mchl]

Some further gains could be possibly achieved by creating index in xml_record_product  on (product_id,product_publication_date). But I am not so sure about it. You also seem to be using FULL TEXT after all?

[dawieharmse]

I tried creating that index, but does not look like it made too much of a difference. Yeah, decided to go with the FULL TEXT, is not the first time I heard/read that LIKE should be used rather sparingly, so decided to remove it.

 

I have another problem, with one of my queries. Would you mind having a look at it?

 

Here is the Query:

SELECT DISTINCT Min(xml_record_supplier.supplier_price) AS LowestSupplierPrice,

xml_record_supplier.supplier_currency_code,

xml_record_product.product_image,

xml_record_product.product_publisher_name,

xml_record_product.product_title AS BookTitle,

xml_record_product.product_id,

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,

xml_record_product.product_imprint,

xml_record_product.product_active,

xml_record_product.product_isFeatured,

xml_record_product.product_isNewArival,

xml_record_product.product_isbn13 AS BookISBN,

xml_category.cat_name As BookCategory,

xml_record_measurement.mes_weight As BookWeight

FROM xml_record_product

Inner Join xml_record_supplier ON xml_record_product.product_id = xml_record_supplier.product_id

Inner Join xml_record_measurement ON xml_record_product.product_id = xml_record_measurement.product_id

Inner Join xml_category ON xml_record_product.cat_id = xml_category.parent_id

WHERE xml_record_product.product_publication_date > '2010/05/17'

Group By xml_record_product.product_id

Order by RAND() DESC LIMIT 4

 

Now, I know using RAND() in large tables are NOT recommended. But what else can I do here?

 

Please also find attached a screenshot of the EXPLAIN for this Query.

 

 

[attachment deleted by admin]

[Mchl]

AFAIR there are some nice ideas in one of these presentations:

http://www.phpfreaks.com/forums/index.php/topic,125105.msg959636.html#msg959636

[dawieharmse]

Thanks a lot.

I will go through it and see what I can come up with.