… this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for filtering records. For…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…pool and a very large number of rows from table2 that do not fit into the buffer pool, then …is being used then, after the selected rows are read from table1, the values of indexed columns that will be used to…to MySQL 5.6. Next interesting thing are the last two columns of the table above and the …
Post: Do you always need index on WHERE column ?
August 28, 2007 By 16 Comments
… see with index the time is by 3.5 times slower. Good that mysql in this case choose do not use index mysql> explain… row in set (10.62 sec) query with index is still 2 times slower. and this time mysql is going to use index in…_something=0; 1 row in set (10.43 sec) So only in the last case we really need the index on column `has…
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
… by using index. It is possibe. So I was shocked and upset to find out it did not. And this optimization is still not… last one is allowed to have “range” clauses, such as >, IN etc. All clauses which follow the range in the index will not use index…=7 will not be using the index (rows with all C values will be retrieved from the index) and if this is not the index covered query…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… more rows to provide result set. In the example above we did order by by last column, in fact index can be used for… | Using where; Using index | +—-+————-+——-+——+—————+——+———+————-+——+———————————+ 2 rows in set (0.00 sec) In this case there is index (k,j) on the table so indexes could be used… end up in slow query log as query which does not use indexes, even if it is quite fast: mysql> explain select * from…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…row_lock_time_maxblogpercona.commanual Innodb_row_lock_waitsblogpercona.commanual Innodb_rows_deletedblogpercona.commanual Innodb_rows_insertedblogpercona.commanual Innodb_rows_readblogpercona.commanual Innodb_rows… last_insert_idblogpercona.commanual Last…log_queries_not_using_indexesblogpercona….
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…, id 18, flags 1, columns 7, indexes 2, appr.rows 2844513 [...] INDEX: name PRIMARY, id 26, fields 2/6, …we’ve used before with page_parser. In the salaries.recovery you can find lot of deleted rows, not only rows …07-31″ 4- Import the rows With the data in our hands the last step is to import it …
Post: Explaining Indexes with a Library Metaphor
August 30, 2011 By 1 Comment
… the library. Not using an index If you are not using the index cards, you would have to go shelf by shelf and row by row, look at… when its not using an index. Using an index You are interested in a book by J.R. Hartley. You go to the index card, you look at section H (categorized by last name of author) and browse through…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 3 Comments
…not supported by the engine. This is true for all MySQL versions, including the latest 5.6 development tree as of last…Indexes on BLOB columns are not supported. While we do not…not used, i.e. if you pay for what you don’t use…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
… BETWEEN and other ranges do not. Using same access type in EXPLAIN …index range scan was initiated. Handler_read_next=42250 means 42250 rows were analyzed during this scan. Basically MySQL started scanning Index…20 order by last_online desc limit 10) ORDER BY last_online desc limit 10; …
