… look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let… Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Multi-Column IN clause - Unexpected MySQL Issue
April 4, 2008 By 20 Comments
… missing optimizer feature. I should not be surprised though as multi-column in is not the most used MySQL feature out there. For… more standard single column IN clause: mysql> explain SELECT url FROM 106pages.106pages WHERE url_crc IN (2752937066,3799762538) AND url in(‘http://members.aye…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… to use multi column indexes. But the problems with Index Merge do not stop there. It is currently rather restricted in which conditions… in set (0.00 sec) As a summary: Use multi column indexes is typically best idea if you use AND between such columns in where clause. Index merge does helps performance but it is far from performance of combined index in this case. In case…
Comment: Multi-Column IN clause - Unexpected MySQL Issue
April 12, 2008 By 20 Comments
… sorry but what is the use on having an CRC in your table?? You could only really figure out IF a… you : hey dude, how many URL’s do you have in your database that points to the website http://members.aye…, www.mysqlperformanceblog,com then table URI stores : 2008/04/04/multi-column-in-clause-unexpected-mysql-issue/#more-361, blabla.html, myblog/entry/bla…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… column in explain plan to see how many index parts are actually used for row lookup. Very common problem I see is multi column… where clauses on the table. In such case you would see “Index” type in explain which correspond to scanning (potentially) complete table in the… of that if you just need access to couple of columns index can be simply much smaller than the data which…
Post: Moving from MyISAM to Innodb or XtraDB. Basics
November 22, 2010 By 2 Comments
…Benchmarks – Make sure to run regression benchmarks in particular in terms of concurrent behavior. You may have… restored backup. Full text search indexes, GIS, multi-column auto increment keys are great examples. There… SELECT COUNT(*) FROM TBL (with no where clause) which is very fast for MyISAM but…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… we tested ranges with and without LIMIT clause, and tested queries which need to read …with only different the values of accessed column is stored in key. MyISAM and InnoDB handle this …multi-versioning. I think this is a big weakness of Falcon, as ‘using index’ is very common optimization we use in…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…for statement duration for Inserts with auto-increment column. This starts giving you problems if you … with multi value inserts however is the following – even if you know number of values in insert …where clause (think for example about running not-indexed UPDATE which has to lock all rows in …
