I have been doing some select tests on a partitioned table and on a non partitioned table, both having same data and around 4 millions records, and size of around 400 Mb each. Mysql version is 5.5.8.
The partition targeted in the partitioned table contains around 300000 records, and the select statement is of type
SELECT DISTINCT field1, field2 FROM table where partition_field3 =text_value
Even if a select operation against the partitioned table is indeed much faster than against the non-partitioned table, I noticed that in 5-10% of the cases it takes a bit too long from the user's perspective.
I was just wondering whether there is a significant difference in partition select performance between Mysql 5.5.8 and Mysql 5.6 since select operations against partitioned tables are not implemented the same way in both versions. In Mysql 5.6, the statement mentions the partition name rather than the "partitioned_column=value" criteria. Does it mean that a select statement against a Mysql 5.6 partitioned table would be somewhat as fast as the "classic" form of the same select statement against a non-partitioned table holding the same data as the partition?
I would appreciate your opinion on that. I found no related benchmarks on the net.