RocketBoom RocketBoom - 1 year ago 85
MySQL Question

MySQL SELECT subquery from same table

I have partitioned by date(timestamp with simple index) table with ~1000000 rows.
I need to get rows by date range + other optional columns criterias.

Can i use subquery somehow for speed optimization?

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download