…as NON_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR ‘,’) as COLUMN_NAMES, s.index_total_pages as…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 5 Comments
…MEMORY storage engine (also known as HEAP) in MySQL has been one of the…MySQL in 2008): The key columns must come before any non-key variable-length columns. For performance reasons, the key columns…900 seconds: 25962452. VmPeak: 6267692 kB Dynamic-row format (DRF): Insertions in 900 seconds: 25949631…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… some MySQL optimizer restrictions in how well it can deal with primary key appended to the index column, especially in such case as… key. After running an analyze on a table with 11M rows I’ve seen cardinality range from 19 to over 19… column MySQL can avoid temporary table or filesort for group by by this column. This works because by scanning data in index order MySQL…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it’s generally applicable… simplicity. Let’s start with this query, which returns zero rows but does a full table scan. EXPLAIN says there are…
Post: Data compression in InnoDB for text and blob fields
May 30, 2012 By 2 Comments
… utilize compressed rows as this appealed to me for this mostly-read application. One quick way you can see if your rows will… length columns (BLOB, VARCHAR, TEXT) in the index record, and the remainder stored in overflow pages. COMPACT became the default after MySQL…? As the fine MySQL manual states: This setting may still be useful for tables with many long BLOB, VARCHAR or TEXT columns, because…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
… constant which is too large to the column instead of simply responding with empty set MySQL truncates the constant, performs the lookup… for unsigned column and I would expect “Impossible Where clause” here Lets look at query execution: | Handler_read_next | 1305742982 | mysql> select….04 sec) | Handler_read_next | 1305819030 | As you can see there were over 70000 row reads performed I came across the bug…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… work. How can I recover deleted rows? We’re going to follow the same example as in my previous post so we… log. mysql (employees) > CREATE TABLE innodb_table_monitor (id int) ENGINE=InnoDB; TABLE: name employees/salaries, id 18, flags 1, columns 7, indexes 2, appr.rows 2844513 [...] INDEX: name PRIMARY, id 26, fields 2…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10. This turned into a… DEFAULT CHARSET=utf8; The table had 22 million rows, with approximately 8 million of them having role_id… testing, as I said before, index condition pushdown. Let’s have a look at the EXPLAIN output: mysql>…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… I’ve created the table with over 30 millions of rows. “val” column in this table has 10000 distinct value, so range… by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select count… with large data sets. In MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL issues IO requests one by…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…I would like to see MySQL memory consumption plotted. I use “VSZ” columns from “ps” output on Linux…. ENGINE: MEMORY NAME: #sql516_1c42_1 TABLE_ROWS: 2 AVG_ROW_LENGTH: 257 DATA_LENGTH: 126984 INDEX_LENGTH:…leak. Dealing with memory leaks is rather complicated as good tools to detect memory leaks like …
