Attempting to speed mysql queries on large tables

Question

I am hitting some performance issues on a Mysql server.

I am trying to query a large table (~500k rows) for a subset of data:

SELECT * FROM `my_table` WHERE `subset_id` = id_value;

This request takes ~80ms to achieve, but I am trying to query it over 20k "id_value", which makes the total execution time of almost 1h. I was hopping that adding an index on subset_id would help, but it's not changing anything (understanding how indexes work, it makes sense).

What I am trying to figure out is if there is any way to "index" the table in a way it wouldn't take 80ms to execute this query but something more reasonable? Or in other work, is ~80ms for querying a 500k rows table "normal"?

Note: On the larger picture, I am using parallel queries and multiple connections to speed up the process, and tried various optimizations changing the innodb_buffer size. I'm also considering using a larger object querying the db once for the 500k rows instead of 20k*xx but having my code designed in a multiprocessed/co-routines/scalable way, I was trying to avoid this and focusing on optimizing the query/mysql server at the lowest level.

Thanks!

As a note, adding "ORDER BY subset_id" seems to speed up the query by 4x — termux
– termux, Commented Dec 14, 2018 at 14:04
@jchevali I am genuinely wondering what made you think of early optimizations — termux
– termux, Commented Dec 14, 2018 at 16:30

Gordon Linoff · Accepted Answer · 2018-12-14 12:29:37Z

1

Use a single query with IN rather than a zillion queries:

SELECT *
FROM `my_table`
WHERE `subset_id` IN (id1, id2, . . .);

If your ids are already in a table -- or you can put them in one -- then use a table instead. You can still use IN

SELECT *
FROM `my_table`
WHERE `subset_id` IN (SELECT id FROM idtable);

answered Dec 14, 2018 at 12:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

5 Comments

Gauravsa Over a year ago

Would also suggest not to use select *, but to include columns explicitly

termux Over a year ago

Thanks, that's a great suggestion and even though not exactly answering my question this is how I think it is going to be implemented eventually.

termux Over a year ago

@Gauravsa thanks for the suggestion, but doesn't seem to improve performances

Rick James Over a year ago

Don't use the IN ( SELECT ... ); use a JOIN instead.

fifonik Over a year ago

Sometimes it can make things worse as simple queries with single id would be cached while more complicated queries with list of random IN(ids) will not be cached (no repeating queries with the same ids, result too bit etc). I do not know exact situation to be sure.

Collectives™ on Stack Overflow

Attempting to speed mysql queries on large tables

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related