How to write delete query with inner join?

Is there a way to write intelligent queries against a merge table in MySQL that represents a bunch of tables partitioned by date?

• I have a set of MySQL tables that are partitioned by date, with a merge table representing all of them. For the query I am writing, the results are joined by date, so rows in table A_2012-12-05 will only join to rows in B_2012-12-05, and rows in A_2012-12-06 will only join to rows in B_2012-12-06, etc. Is there a way to write an intelligent query so that MySQL won't go looking for rows in B_2012-12-06 to join with rows in A_2012-12-05?

• Answer:

  What you want is http://dev.mysql.com/doc/refman/5.5/en/partitioning-pruning.html, which is not supported by Merge tables.  You need to use partitioned tables instead. Read the fine manual about MySQL Partitioning, because there's a few gotchas to learn. Or read this nice presentation about MySQL Partitioning: http://www.slideshare.net/PerconaPerformance/boost-performance-with-my-s-q-l-51-partitions Then use the excellent http://datacharmer.blogspot.com/2008/12/partition-helper-improving-usability.html tool too.