Search code examples
mysqlsubquerypartitioningdatabase-partitioning

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 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.

Solution

  • 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.