…. It’s fairly straightforward to tell when MySQL is doing this; run an EXPLAIN on your SELECT and you’ll see “index… BY user_id LIMIT 1; The users table in question here had approximately 4.5M rows, and an EXPLAIN produced the following… parent_id=0 AND status=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user_type…
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
… for removing redundant indexes Here is example from NNSEEK database: mysql> explain SELECT thread_id FROM nn2_msg132.msg132 WHERE group_id… ’2006-12-02 23:59:59′) ORDER BY published desc LIMIT 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE…
Post: Neat tricks for the MySQL command-line pager
June 23, 2008 By 13 Comments
… scary how predictable I am sometimes, isn’t it?) mysql> pager less mysql> show innodb status\G For big result sets, it’s… you can write your own, that’s not really a limitation.(Edit: I’m wrong! You can. See Giuseppe’s comment… goodies can we think of here? mysql> pager mk-visual-explain PAGER set to ‘mk-visual-explain‘ mysql> explain select * from sakila.film inner join…
Post: Joining many tables in MySQL - optimizer_search_depth
April 20, 2012 By 8 Comments
…subqueries these might need to be executed during EXPLAIN phase yet making it unusable to check …found the following explanation from Timour Katchaounov in MySQL mailing list archives I have some recollection …picks value of min(number of tables, 7) essentially limiting search depth to no more than 7 …
Post: Upgrading MySQL
January 5, 2010 By 24 Comments
…, such as DECIMAL field got whole another meaning in MySQL 5.0 MySQL generally supports legacy storage format for a while, even… slower checking your progress along the way. There is no limit as Safe you can get so I would just outline… run SELECT queries on both new and old MySQL installations and check result set, explain plan and execution time, reporting you on…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
… of double buffering and I can save the limited amount of file system cache I would normally … files, binary log, FRM files, MySQL MyISAM system tables etc. Starting MySQL 5.5 MySQL uses asynchronous IO which should …run in O_DIRECT mode. This can’t be explained by extra cache as 1GB of OS cache …
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 26 Comments
… the following topics: What is a materialized view(MV)? It explained that an MV can pre-compute joins and may aggregate… the trigger is immediate and affects every transaction. Finally, MySQL has limited trigger support, some of which is the cause of the….test_demo. This because the source table is `test`.`demo`. mysql> select * from flexviews.test_demo\G *************************** 1. row *************************** dml_type: 1…
Post: MySQL on Amazon RDS part 1: insert performance
March 15, 2011 By 37 Comments
… Service (RDS) is a cloud-hosted MySQL solution. I’ve had some clients hitting performance limitations on standard EC2 servers with EBS… shouldn’t have published this result without being able to explain exactly what was happening on the server. I will reproduce…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… in set (1.01 sec) mysql> explain select * from idxitest where a=100 order by id desc limit 1; +—-+————-+———-+——-+—————+———+———+——+——+————-+ | id | select_type | table… can look something like this: mysql> explain select * from idxitest where a=100 order by id desc limit 1; +—-+————-+———-+——+—————+——+———+——-+——+——————————————+ | id | select_type | table…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… the way, the slow query logs in the stock MySQL server are extremely limited, and for serious analysis it’s basically mandatory… information in the slow query log output. The stock MySQL server’s limitation of one-second granularity makes it hide problem queries… (execution times, etc), the EXPLAIN plan, and the desired modifications to the query or the table. We explain how to interpret what…
