I am using OR in mysql query to check different values at same time, I found results are coming very slow and here I am joining two tables also, I am unable to check which is taking long time.
Query here:
select l.*, l.category as lid
FROM listings AS l
LEFT JOIN listprices as pr ON pr.product_id=l.id
where (pr.price >= 100 AND pr.price <= 1000)
OR (pr.price >= 1001 AND pr.price <= 5000)
OR (pr.price >= 5001 AND pr.price <= 10000)
order by pr.price ASC
query working fine but results taking too much time. If any other alternative to get fast. I have heard OR taking much time to execute results.
MySQL doesn't handle OR clauses very well. (Correctly, but slowly.) In your case it seems like the presence of your sequence of OR clauses is defeating the use of your index on listprices.price. That will make your performance stink. Let's try to refactor this query to avoid that sequence of OR clauses.
Notice that you have a LEFT JOIN x ... WHERE x.column ... pattern in your query. That converts your LEFT JOIN to an inner JOIN.
First, let's do a subquery to get a list of listprices.product_id values with matching prices. That goes like this:
SELECT price, product_id FROM listprices pr WHERE pr.price >= 100 AND pr.price <= 1000
UNION
SELECT price, product_id FROM listprices pr WHERE pr.price >= 1001 AND pr.price <= 5000
UNION
SELECT price, product_id FROM listprices pr WHERE pr.price >= 5001 AND pr.price <= 10000
These three queries (unioned together) each can be made very fast by a compound index on price, product_id. You'd be wise to test this query to ensure it's giving the result you want.
Then you will use this query as a subquery; that is, as if it were itself a table:
select l.*, l.category as lid
FROM listings AS l
JOIN (
SELECT price, product_id FROM listprices pr WHERE pr.price >= 100 AND pr.price <= 1000
UNION
SELECT price, product_id FROM listprices pr WHERE pr.price >= 1001 AND pr.price <= 5000
UNION
SELECT price, product_id FROM listprices pr WHERE pr.price >= 5001 AND pr.price <= 10000
) pr ON pr.product_id = l.id
order by pr.price ASC
This should get you the result you want more quickly.