… 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 17 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 141 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 28 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 30 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…
Comment: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
… row lookup speed. Innodb uses alternative approach with adaptive hash indexes. Two sizes: Right if you say max-size*0.9… plan some changes in this aspect for Beta. Protection from partial page writes: Checksum can be done optional. Oracle and Innodb… database. Some other techniques however are needed to prevent from partial page writes breaking database. No uncommitted changes to the disk…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… will be corrupted by unproper shutdown, it will fail with partial statement execution if certain errors are discovered etc. You should… later cause crashes wrong query results and further data corruption. Partial updates. MyISAM is does not have transactions this is well… improve performance dramatically in certain cases by avoiding flushing dirty index blocks from key_buffer to disk, but it also comes…
