… 16 minutes. Then we added another MySQL function for different set of attributes and indexing speed went from 16 minutes to 26…_junk_mysql(author), ltrim_junk_mysql(title) from paintings limit 100000; 100000 rows in set (2.97 sec) mysql> select remove_dups_mysql(author), remove_dups_mysql…
Post: Innodb vs MySQL index counts
November 29, 2011 By 6 Comments
…/table_name contains 8 indexes inside InnoDB, which is different from the number of indexes 7 defined in the MySQL This customer was… found on my test table that the extra index in Innodb could be removed by doing: ALTER TABLE table_name ENGINE=Innodb… whole table based on the .frm table definition and removes the existing index in Innodb, which might not be desirable, but at…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… index lookup is being perform but as only actual rows which are found and returned up to the top level MySQL part… results. What you can think about in this case is removing group by and aggregate functions. Then query would become “select… query sends after group by, distinct and aggregate functions are removed (A) – look at number of rows examined divided by number…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… given SELECT would show N possible index choices but only one index actually used. To some extent, MySQL 5.0 and later changed…-column indexes on them, MySQL could sometimes make use of the multiple indexes. For instance, “SELECT foo FROM bar WHERE indexed_colA = X OR indexed… have parent_id = 0, we might be tempted to remove the index on parent_id if queries for parent_id != 0 could…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…index_usage’ database contains several tables: mysql> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes…
Post: Connecting orphaned .ibd files
May 13, 2011 By 19 Comments
…several situation when you have to: 1. ibdata1 is erroneously removed 2. ibdata1 is heavily corrupted and innodb_force_recovery …is equal to 15 in all actor’s indexes: mysql> select * from INNODB_SYS_INDEXES WHERE TABLE_ID = 13G *************************** 1. row *************************** TABLE_ID…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | …your connections might have created and forgot to remove (or still use for processing). Of course …
Post: Finding your MySQL High-Availability solution – The questions
October 16, 2009 By 36 Comments
… Full text indexes and GIS indexes that are supported only by MyISAM. The HA solutions that work well with MyISAM are “MySQL replication…, the MyISAM Full text indexes might be replaced by another search engine like Sphinx in order to remove the restriction. There is… based on replication that handles GIS indexes. HA solutions Need MyISAM Full text or GIS indexes “MySQL replication” and “Master-Master with MMM…
Post: MySQL 5.6.7-RC in tpcc-mysql benchmark
October 8, 2012 By 22 Comments
… into two bugs: MySQL 5.6.7 locks itself on CREATE INDEX MySQL 5.6.7-rc crashed under tpcc-mysql workload But at… the result is taken only for last 2000 sec to remove warm-up phase. Measurements are taken every second. Parameters to… change the result significantly, especially in intensive IO workloads. In MySQL 5.6 it is finally possible to achieve stable throughput…
Post: Migrating to XtraDB Cluster Webinar follow up questions
June 6, 2012 By 3 Comments
… any single node in the cluster. Q: Are adding/updating mysql user accounts replicated? Yes, provided you use CREATE USER or GRANT. DML on the mysql.* tables directly will not be replicated unless you are using… I said, some ALTER TABLE operations, such as adding and removing indexes, can be done using Galera’s rolling schema upgrade feature…
