CODEWITHSUNDEEP
mysqlsqlperformancejoinquery-optimization
Andrii Kovalenko
Andrii Kovalenko

Reputation: 2227

How to speed up MYSQL Query with JOIN and BETWEEN where clause?

The query

SELECT SQL_CALC_FOUND_ROWS wp29174960179_posts.*
    FROM wp29174960179_posts
         JOIN wp29174960179_plugin_wpf_products_attribute_79_numbers pan ON pan.product_id = wp29174960179_posts.ID
    WHERE pan.number BETWEEN 10 and 10000
    ORDER BY wp29174960179_posts.post_date ASC LIMIT 0, 9;

This query so slow (takes 2s). Expected execution time <= 100ms

There is 80 000 records in wp29174960179_plugin_wpf_products_attribute_79_numbers table.

And 100 000 records in wp29174960179_posts table

There are 2 indexes for wp29174960179_plugin_wpf_products_attribute_79_numbers table

ix__number__product_id (product_id, number)
ix__product_id__number (number, product_id)

There is index for *wp29174960179_posts for ID field

Despite these indexes. Query takes 2-3 seconds

SQL full Code Snippet link:

https://www.db-fiddle.com/f/4Vk97FhArBVJ1Eb1BAubNB/0#&togetherjs=8KQQacE4Vt

Upvotes: 1

Views: 76

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

My guess is that the outer sorting is causing the performance issue. Your query and indexes look reasonable. The question is: can we trick MySQL into avoiding the sort?

Start with the rewrite that GMB suggests. This also has the nice characteristic that it does not return duplicated values:

select p.*
from wp29174960179_posts p
where exists (select 1 
              from wp29174960179_plugin_wpf_products_attribute_79_numbers n 
              where n.product_id = p.id and
                    n.number between 10 and 10000
             )
order by p.post_date 
limit 9;

Then try the following indexes:

  • wp29174960179_posts(post_date, id)
  • wp29174960179_plugin_wpf_products_attribute_79_numbers(product_id, number)

The hope is that the posts table will be scanned in post_date order and the first 9 matching rows will be returned. Fingers crossed.

Upvotes: 1

GMB
GMB

Reputation: 222482

It looks like you have a 1-N relationships between posts and pans, and you are trying to get the posts for which rows in pan exists that satisify a given condition.

If so, you could try and rewrite the query with an EXISTS subquery:

select p.*
from wp29174960179_posts p
where exists (
    select 1 
    from wp29174960179_plugin_wpf_products_attribute_79_numbers n 
    where n.product_id = p.id and n.number between 10 and 10000
)
order by p.post_date 
limit 9

This technique avoids the need for aggregation in the outer query. This query should be able to take advantage of an index on wp29174960179_plugin_wpf_products_attribute_79_numbers(product_id, number).

Upvotes: 3

Related Questions