…10. Now let’s come to the MySQL configuration. MySQL Configuration I used Percona Server 5.5.22…rows inserted. I noticed that the insert rate drop was mainly caused by IO pressure caused … As we all now there are pros and cons to using secondary indexes. While secondary indexes cause read performance…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…we have traded many point index lookups to one or more index range lookups. This means MySQL can employ many …rows from t1 would be read and then for all rows, index lookup would be performed on t2. Here …buffer_size were set to 6M, the query time dropped to ~40s. So the maximum possible benefit is …
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… were accessed: mysql> select concat(‘alter table ‘,d.table_schema,’.',d.table_name,’ drop index ‘,group_concat(index_name separator ‘,drop index ‘),’;') stmt from (…testing allowing to drop all potentially not needed indexes so you can perform proper QA and ensure you really did not drop anything …
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…index creation uses temporary files in tmpdir for all indexes…index as the clustered one. References: Peter’s post MySQL bug #57583 MySQL bug #49120 Fast Index…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the … variable reference for all three sites in …drop_eventblogpercona.commanual Com_drop_functionblogpercona.commanual Com_drop_indexblogpercona.commanual Com_drop_procedureblogpercona.commanual Com_drop_serverblogpercona.commanual Com_drop_tableblogpercona.commanual Com_drop…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…Happy New Year all blog readers ! Now back to HardCore MySQL business – foreign Keys. MySQL supported …is able to drop indexes without rebuilding table since MySQL 5.1 (Innodb Plugin) dropping foreign keys ….05 sec) mysql> drop table parentx; Query OK, 0 rows affected (0.61 sec) mysql> show create…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
…| +——–+———-+———-+——————————————————————-+ 2 rows in set (4 min 5.52 sec) mysql> alter table a add key(c); Query OK…indexes and bringing box down. You can also use this trick for ALTER TABLE which requires table rebuild. Dropping all indexes…
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
…index are the most active or which are not being used at all and could be candidates for dropping. All…0.00 sec) mysql> SHOW INDEX_STATISTICS LIKE ‘hosting2%’; +——————————————–+———–+ | Index | Rows_read | +——————————————–+———–+ |…
Post: UNION vs UNION ALL Performance
October 5, 2007 By 13 Comments
… performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION… where i=5 union all select * from test.abc where j=5 and i!=5 The query time dropped to about 6… clause. I also tried this original query (which uses index merge method in MySQL 5.0): select * from test.abc where i…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
…surprised by serve drop in performance. This especially apples to index lookus and …all indexes are created equal. Some indexes may be placed in sorted way or pages placed in random places – this may affect index… MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL …
