… the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL… take a look briefly at what this enhancement actually is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index… index parts after the part on which range condition is applied cannot be used for filtering records. For example, suppose you have a…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…how many times the buffer used by MRR had to be reinitialized, because the buffer was small and not all index… causes the counters Handler_read_key and Innodb_rows_read to be incremented by TWO. It does …clearly a part of the optimizer’s job to select the best query execution plan. I had noted a …
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… BKA is being used then, after the selected rows are read from table1, the values of indexed columns that will be used to perform a lookup on table2 are batched together and sent to…_orderdate LIMIT 10; In-memory workload Now let’s see how effective are the join optimizations when the workload fits entirely…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… at Oracle, MariaDB team or Drizzle team to take a look into solving this problem. You …_schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length… #sql516_1c42_1 TABLE_ROWS: 2 AVG_ROW_LENGTH: 257 DATA_LENGTH: 126984 INDEX_LENGTH: 0 CREATE_TIME: …
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…a shared gap lock preventing the insertion of data to other sessions. How to troubleshoot gap locks? Is possible to detect those gap locks using…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
…to find the different indexes and types. mysql (information_schema) > select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES…use constraints_parser to recover the rows in a human readable format. ~/recovery-tool# ./constraints_parser -5 -D -f pages-1329221407/FIL_PAGE_INDEX…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
…row lookup. Very common problem I see is multi column indexes which are used but only to their short prefix which is not very selective. A… 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 merge” Using Index to …
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 28 Comments
…SELECT queries might have to scan through lots of obsolete, but not-yet-purged, row versions. Later, the purge thread will have to…rows were small). But how to do this without splitting the file? The answer lies in the Unix fifo. I created a…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
…spoke about how MySQL uses the index but not exactly what it gets from the index – typically (unless it is covering index) MySQL gets a “row pointer…no choice but only to use combined index: mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index(i1,i2) where …
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…how to make MySQL Optimizer to do what we want to do – Just add yet another table to…Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: g type: index possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 10 Extra: Using index…
