… traditional secondary index lookups, if the columns that are being fetched do not belong to the secondary index definition (and hence covering index optimization… note that the following changes were made in the MySQL config: optimizer_switch=’index_condition_pushdown=off’ optimizer_switch=’mrr=on’ optimizer… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…you can test it, though it does not cover everything. For prepared statements you might want …tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+…
Post: Covering index and prefix indexes
November 23, 2006 By 5 Comments
… Now lets see if index can be used as covering index if it has some key parts which are prefixes: mysql> explain select k… parts as covering index if you do not touch columns which only have prefixes in the index. Notice “Using Index” in Extra column. mysql> explain… in the query and if it does, covering index can’t be used. Note: MySQL is however smart enough to make sure prefix…
Post: Explaining Indexes with a Library Metaphor
August 30, 2011 By 1 Comment
… title in the library. Not using an index If you are not using the index cards, you would have to go shelf… on the hard disk when its not using an index. Using an index You are interested in a book by J.R… saved yourself from walking over to the shelves. Using a covering index You are interested to know how many pages a certain…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… multi-column index on (AGE,STATE). Lets see why it is the case. MySQL indexes are (with few exceptions) BTREE indexes – this index type is… index lookups. So we spoke about how MySQL uses the index but not exactly what it gets from the index – typically (unless it is covering index) MySQL…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… how MySQL uses single index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index… couple of columns index can be simply much smaller than the data which is one of the reason covering indexes help to speed up queries even if data is in memory. If MySQL is only reading index and not…
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
… order and it will well be. This is how MySQL will optimize indexed ORDER BY col DESC queries for example. Reverse scan… using the index (rows with all C values will be retrieved from the index) and if this is not the index covered query you might rather shorten your key to KEY(A,B) to keep index smaller. The good news are Loose index…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 41 Comments
… fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set (0.00 sec) mysql> select count(val) from fact; +————+ | count(val) | +————+ | 7216582 | +————+ 1 row in set (1.17 sec) mysql> select count(val2) from fact; +————-+ | count(val2) | +————-+ | 7340032 | +————-+ 1 row…
Comment: Duplicate indexes and redundant indexes
September 22, 2006 By 38 Comments
… at least in MySQL, the prefix redundancy problem just cannot occur for RTREE Spatial index, because they cannot be composite: mysql> create table… a longer non-unique and non-covering index could give worse performance over a shorter unique index that is the prefix of the… would not have been wasted if the longer index would’ve been a covering index?) I think that is an intersting case too…
Post: How number of columns affects performance ?
September 28, 2009 By 10 Comments
… wide tables is to use covering indexes. I added one to t99v1 table and repeated the query: mysql [localhost] {msandbox} (test) > select max… of transaction control overhead attached to them. Also note the covering index scan speed is very similar to full table scan speed… expected as table data is stored in BTREE index very similarly to how indexes are stored. Summary: Beware of dynamic row format…
