MySQL SELECT subquery from same table

Question

I have partitioned by date (timestamp with simple index) table with ~1000000 rows.

I need to get rows by date range + other optional columns criteria.

Can I use subquery somehow for speed optimization?

For example: select rows by date range (it will be quite fast by index) at first in subquery, and after that select rows by other criteria from result temp table.

Please provide `SHOW CREATE TABLE` and a sample `SELECT`. There are many subtle things in the schema and/or select that need to be analyzed. — Rick James, Sep 27 '16 at 00:34
An example of what to do 'wrong' is put the timestamp first in any index. — Rick James, Sep 27 '16 at 00:34

score 0 · Accepted Answer · answered Sep 25 '16 at 15:08

There is no need for a subquery for further limiting your output to match your criteria. You can safely include all the constraints within one select block and let the optimizer do the job of choosing index (if there are many and it is applicable) to scan in order to retrieve correct results in an effective manner.

Use EXPLAIN to investigate the execution plan of your queries and do your own comparison. Please refer to MySQL manual to find out more about how the engine will execute your statement. More on query optimization can be found in section 9.8.

MySQL SELECT subquery from same table

1 Answers1