Sergey Glukhov (Gluh) recently wrote an interesting blog about InnoDB secondary key improvements in MySQL 5.6. His blog isn't aggregated to planet.mysql.com but certainly deserves some attention.
Here it is: InnoDB, extended secondary keys.
3581 Marko Makela 2011-08-10
Bug#12835650 VARCHAR maximum length performance impact
row_sel_field_store_in_mysql_format(): Do not pad the unused part of
the buffer reserved for a True VARCHAR column (introduced in 5.0.3).
Add Valgrind instrumentation ensuring that the unused part will be
Thread 19:The modifications we did to the server to remedy the problems can be divided into two categories as described below. In the end, the result was significantly improved performance.
Syscall param pwrite64(buf) points to uninitialised byte(s)
at 0x381C60EEE3: ??? (in /lib64/libpthread-2.12.so)
by 0x84B703: my_pwrite (my_pread.c:162) by 0x86FE6F: mi_nommap_pwrite (mysql_file.h:1203)
by 0x88621B: _mi_write_static_record (mi_statrec.c:65)
by 0x889592: mi_write (mi_write.c:142)
by 0x532085: handler::ha_write_row(unsigned char*) (handler.cc:6043)
by 0x69B7F7: end_write(JOIN*, st_join_table*, bool) (sql_select.cc:20237)
by 0x69A43C: evaluate_join_record(JOIN*, st_join_table*, int)
by 0x69A8E0: sub_select(JOIN*, st_join_table*, bool) (sql_select.cc:19294)
The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to [the value of variable]. If set to 0, index dives are always used."Equality range" means predicates using operators IN() or =, and it's important to notice that the number of such ranges is counted on a per index basis. Furthermore, index dives are not used on unique/primary indexes, so only non-unique indexes are affected.
SELECT title FROM albums WHERE artist IN (10, 12)But some MySQL users have queries with hundreds of values in the IN clause. For such queries it may take considerably longer to optimize the query than to execute it. This is when it makes sense to use the less accurate index statistics. Consider this example: