… Innodb uses – You can do 100 partial random dives in the index tree for each index, you either reach the leaf page in…. This would get you approximate in memory fit for each index and it can be refreshed every so often. Now of…
Comment: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
I think it is worth to note even though ICP improves the scan speed of the range in the index by allowing scanning index entries only and not reading the rows, it still scans complete range when partial scan is possible. In the example above “l_partkey = x and l_quantity >= 1 and l_quantity
Comment: Multi Column indexes vs Index Merge
March 12, 2010 By 30 Comments
… multiple column keys. I created an partial index for person table of 2M+ records: CREATE INDEX firstlastname_idx ON person(firstname(2), lastname… query execution. I do run explain and it using the partial index. I know there is a issue about mysql on warming…
Comment: Picking datatype for STATUS fields
August 11, 2008 By 24 Comments
… case assuming you typically do lookups for that 1% value index can be quite helpful. Though too bad with MySQL you… that same value junk in the index. If MySQL would support partial indexes we could build indexes to only include the selective status values… is using separate table to maintain such values (with proper indexes) so they can be looked up without requiring to have…
Comment: Do you always need index on WHERE column ?
August 29, 2007 By 16 Comments
…. It is well possible to have column with 2 values indexes if one of them is in 99% of values and… 1% and have index well used to retrieve the data for that 1% Too bad MySQL does not have partial indexes though (I mean so only that 1% could really be indexed)
Comment: Why MySQL could be slow with large tables ?
November 27, 2008 By 139 Comments
A count will always take more. In real DBs like PG, you can set an index on the count too, as well as several “partial indexes“.
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… optimizing-by-counter never worked! A partially valid use of Key_reads There is a partially valid reason to examine Key_reads….” And now, I would finally like to show you something partially useful you can do with Key_reads: [baron@localhost ~]$ mysqladmin… that is large enough to hold your working set — the index blocks that are frequently used. How large is that? This…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
…, which people do quite frequently with MySQL 
[+] Compact Indexes Compact Indexes are great. Huge indexes is where large portion of Innodb performance problems… where detailed documentation is missing so far. [-] No protection from partial page writes This means if single page write was not…
Post: MySQL Crash Recovery
July 30, 2006 By 26 Comments
… error rather than stalling forever which allows application to become partially functional as soon as possible. This hack is needed only… Innodb dictionary but .frm not created or not completely written. Partially written .frm files or .frm being unsync with internal Innodb… extreme load. Innodb statistics – Unlike MyISAM Innodb does not store index cardinality in tables, instead it computes them on first table…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… passed to the next step – there could be some non-index based filtering applied. The other reason for difference is join… enough to perform sorting or use temporary table with partial result set (partial rows) and than compose full rows by reading data…, while retrieval of large rows from uncached table by random index values (or even range) may be perfoming at some 100…
