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: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
… thing to keep into account – INSERT … SELECT actually performs read in locking mode and so partially bypasses versioning and retrieves latest committed… compared to what pure SELECT would give. This by the way applies to SELECT .. LOCK IN SHARE MODE and SELECT … FOR UPDATE as… example if you’re doing batch processing which is well indexed you might chop transactions and process rows by small bulks…
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… searching on the first character on the firstname and lastname. select firstname, lastname from person where firstname like ‘p%’ and lastname…
Comment: Do you always need index on WHERE column ?
August 29, 2007 By 16 Comments
… remember about selectivity – it is actually selectivity of value what matters. It is well possible to have column with 2 values indexes if… 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: 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… same value junk in the index. If MySQL would support partial indexes we could build indexes to only include the selective status values which it… is using separate table to maintain such values (with proper indexes) so they can be looked up without requiring to have…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
…select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——-+—————+———+———+——+——+————-+ | 1 | SIMPLE | lt | index | NULL | PRIMARY | 4 | NULL | 1600 | Using index… with partial result set (partial rows) …
Post: Using MyISAM in production
June 17, 2006 By 35 Comments
…crashes wrong query results and further data corruption. Partial updates. MyISAM is does not have transactions …which is blocked by long running select will also block further selects from this table – they will … in certain cases by avoiding flushing dirty index blocks from key_buffer to disk, but …
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… IN(23,545,654,34) > instead of: > SELECT * FROM articles WHERE user_id IN (SELECT id FROM users WHERE featured=1) Ouch… and returned. SELECT * FROM articles INNER JOIN users ON users.id = articles.user_id AND users.featured = 1 ; > Use Indexes This item… your queries remember > we’re here to help. Partial agreement: “Use Indexes smartly.” Indexing columns that have very low cardinality will often be…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
…do quite frequently with MySQL 
[+] Compact Indexes Compact Indexes are great. Huge indexes is where large portion of … modes is supported, including no support for SELECT FOR UPDATE. This is also why Falcon … is missing so far. [-] No protection from partial page writes This means if single page …
Post: MySQL Crash Recovery
July 30, 2006 By 26 Comments
…than stalling forever which allows application to become partially functional as soon as possible. This hack …Innodb statistics – Unlike MyISAM Innodb does not store index cardinality in tables, instead it computes them …. To warmup this data you might run select 1 from _table_ limit 1 for each …
