Way to optimize huge SQL Query

Question

I have a very long query which return some records if the column id_cat matches user search.

It looks like this:

SELECT * 
  FROM table 
 WHERE (id_cat = 50 OR id_cat = 51 OR id_cat = 52...) up to some high numbers.

In above case the query gets real big which increases it time for response.

I need to optimize that because response time get close to 15 seconds (basically it search through all of the categories).

How should I do this?

First of all i was thinking of building a query like below:

SELECT * FROM table WHERE (id_cat = (50 OR = 51 OR = 52))

Which should decrease the number of sql words but it doesnt work like this (returns null instead of rows). And I dont have any more ideas.

Do you have any suggestions about how to handle that?

Consider building a separate table as a list of all the ids, and then comparing against that — Strawberry
– Strawberry, Commented Dec 6, 2019 at 19:05

Gordon Linoff · Accepted Answer · 2019-12-06 17:14:53Z

4

First, build the query using in:

SELECT *
FROM table 
WHERE id_cat IN (50, 51, 52, . . .);

MySQL optimizes IN with constant values. Without an index, there is still a full table scan. So, I also recommend an index on table(id_cat).

answered Dec 6, 2019 at 17:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Marcin Over a year ago

Thank. This was the answer i was searching for.

Neville Kuyt Over a year ago

And if you can turn at least some of those into a BETWEEN query, it becomes easier to read

Marcin Over a year ago

@NevilleKuyt unfortunately in that case BETWEEN would not work for me.

Rick James Over a year ago

'OR' is optimized into IN.

Gordon Linoff Over a year ago

@RickJames . . . By any chance, do you have a reference for that? I can find the reference that IN lists are sorted (dev.mysql.com/doc/refman/8.0/en/…), but not that OR is converted to IN. Based on benchmarks I've seen comparing them, this has not been true in older versions of MySQL.

|

Collectives™ on Stack Overflow

Way to optimize huge SQL Query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related