June 20, 2013

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

…number generator picks values in…columns in a compound index, then the order would matter, and it would have to match the order of columns in…in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order.  This made the first table in…

Filed Under: Insight for Developers, MySQL, MySQL Webinars Tagged With: , , ,

Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available

April 17, 2013 By 2 Comments

…MySQL port would be used when port number wasn’t explicitly defined in…columns would be used in foreign key definitions. Bug fixed #1100496 (Seppo Jaakola). Debian packages included the old version of innotop. Fixed by…

Filed Under: MySQL, XtraDB Cluster Tagged With: , ,

Post: How to recover table structure from InnoDB dictionary

April 22, 2013 By 4 Comments

… shift all values by a byte and thus…. `LEN` is the maximum number of bytes a field… let me know. mysql> select * from SYS_COLUMNS WHERE TABLE_ID=741; +———-+—–+————-+——-+———+——+——+ | TABLE_…Index id in our example is 1679: mysql> SELECT * FROM SYS_FIELDS WHERE INDEX…

Filed Under: Insight for DBAs, MySQL Tagged With: , , , , , ,

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

… a large number of MySQL 5.0 and 5.1 installations that would upgrade or are in the process of upgrading to MySQL 5… rows in set (0.00 sec) session6 > ALTER TABLE test_2 DROP COLUMN c; session7 > select * from test_2 order by id; session8 > select * from test_2 order by id; session9 > select * from test_2 order by id; session10 > select…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , , ,

Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown

March 14, 2013 By 2 Comments

… impacting) optimizer issue: a “SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10. This… (just one column) and can take advantage of the covering index technique, did actually take more time to execute: mysql> SELECT role_id… number of “Handler_read_next” calls was reduced from 8346769 (5.5) to just 266 (5.6), reducing the executing time by…

Filed Under: Events and Announcements, Insight for Developers, MySQL Tagged With: , ,

Post: How to debug long-running transactions in MySQL

March 8, 2011 By 6 Comments

… convert this into a query log, and examine the log. In MySQL 5.1 and newer with the InnoDB plugin, it’s… ID in SHOW PROCESSLIST, and look in the Host column; there you’ll see a hostname followed by a port number. That is the port number… (${full}%, ${avail}MB free)”>&2 exit 1 fi host=$(mysql -ss -e ‘SELECT p.HOST FROM information_schema.innodb_lock_waits w…

Filed Under: Insight for DBAs, MySQL

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

…: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer each pk_column… be seen by the numbers reported for the status counter Innodb_rows_read. While MySQL 5.6 is also reporting increased numbers for the…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5

April 4, 2012 By 4 Comments

…used then, after the selected rows are read from table1, the values of indexed columns that will be used …1 1 The first obvious improvement is shown by the high numbers of Innodb_buffer_pool_read_ahead when … However, there is one number in MariaDB 5.5 that is quite large as compared to MySQL 5.6 and …

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…in MySQL 5.6 (part 1) in MySQL 5.6 is syntactically identical to MyISAM full-text search, in…FT index by declaring a column called FTS…| OK | +———————-+———-+———-+———-+ 1 row in set (4.60 sec) mysql> select * from information_schema.innodb_… that the number of rows in I_S…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

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 to… full table scan. EXPLAIN says there are no possible_keys. SELECT * FROM tbl WHERE status=’waiting’ AND source=’twitter’ AND no…

Filed Under: Insight for DBAs Tagged With: ,
« Older Posts