… set (0.00 sec) mysql> select * from innodb_sys_indexes where table_id=13; +———-+———+———-+——+———-+———+——-+ | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO… set (0.00 sec) mysql> select * from innodb_sys_indexes where table_id=18; +———-+———+———-+——+———-+———+——-+ | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
…mysql> explain select count(*) from trunc where i=4147483647; +—-+————-+——-+——+—————+——+———+——-+——-+————————–+ | id | select_type | table | type…
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
… — system files, binary log, FRM files, MySQL MyISAM system tables etc. Starting MySQL 5.5 MySQL uses asynchronous IO which should allow it… given workload the performance was best with both read_ahead types enabled but it remained much better in buffered mode. Take… as more information MySQL has about data which will be accessed next – in case of full table scan, index scans etc the…
Post: Performance gotcha of MySQL memory tables
February 1, 2008 By 5 Comments
… it is the only MySQL storage engine which defaults to HASH index type by default, instead of BTREE which makes indexes unusable for prefix… which again comes from the fact MEMORY tables use HASH indexes by default. I’ve created rather similar test table: CREATE… bad to be true, but there is a reason. HASH index stores list of matching values for each hash value. In…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… concurrently many times or even workload consisting of several query types. Though it is rather rare for this to be needed… it without side load. Setting up 2 MySQL Servers side by side (for example with MySQL Sandbox) can especially be helpful. Once… SHOW INDEXES FROM for tables involved and check cardinality) Different stats can often cause different plans. Run ANALYZE TABLE on both MySQL…
Post: Percona Live MySQL Conference and Expo 2013: The talks I want to see
February 12, 2013 By 4 Comments
…) before. MySQL Query Anti-Patterns that can be moved to Sphinx - (Vladimir Fedorkov, PalominoDB) Now that Innodb supports Full-text indexing, I…) – This is right up my alley in terms of the type of work I generally do. I’m really interested to… instruction about what some migration strategies might look like. Using MySQL Performance Schema to debug performance issues - (Zburivsky Danil, Pythian) – I…
Post: MySQL Upgrade Webinar Questions Followup
June 28, 2012 By 7 Comments
… to MySQL 5.5 should be rather safe from data standpoint. There are still possibilities for sorting order and data type related changes, so you still need to run mysql_upgrade to check if any tables… I would recommend using Percona Server with enabled expand_fast_index_creation feature, as I just wrote this option can help…
Post: What's required to tune MySQL?
July 27, 2011 By 4 Comments
…’t something you should expect to deliver huge wins. If MySQL is actually badly configured, you can hurt its performance significantly… cases, so keep in mind that the vast majority of MySQL installations don’t suffer from the variety and severity of… The workload The queries The schema design (table design, data types, indexing, etc) The data itself All of these things interact, and…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… 0 mysql> show index from antest; +——–+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
…INDEX(a) to force MySQL optimizer using right plan. These results mean you should be very careful applying index…
