… 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: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… single column BTREE index MySQL will dive into the index to find first matching row and when will continue scanning index in order until… 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: 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: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
…serve drop in performance. This especially apples to index lookus and joins which we cover later. As everything usually slows down …scan by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… further index parts. If you have A BETWEEN 5 AND 10 AND B=5 for the same index MySQL will use the index… but… 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: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… data from the index. MySQL can ether read index only for all rows, in this case you will see “Using Index” in EXPLAIN output… point. So MySQL Will not use indexes in all cases when it is technically possible. For multiple key part indexes MySQL will only be… expect – why range which covers 5 rows is hundreds of times slower than single row ? If MySQL Optimizer would handle this case…
Post: Finding your MySQL High-Availability solution – The questions
October 16, 2009 By 36 Comments
… and many deployments use combination of solutions. I will not cover ScaleDB and Continuent because I know almost nothing of these… Full text indexes and GIS indexes that are supported only by MyISAM. The HA solutions that work well with MyISAM are “MySQL replication… based on replication that handles GIS indexes. HA solutions Need MyISAM Full text or GIS indexes “MySQL replication” and “Master-Master with MMM…
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…
