…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: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied…
Post: UDF -vs- MySQL Stored Function
September 6, 2010 By 6 Comments
… 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: 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 12 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: 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: Bug#12704861
November 20, 2011 By 1 Comment
… potentially be a very serious problem that is fixed in MySQL 5.1.60. I’ll repeat here the full documentation…_page_alloc_low(). If a clustered index leaf page was previously freed in mtr, remove it from the memo of previously freed…. buf_reset_check_index_page_at_flush(): Remove. The function fsp_init_file_page_low() already sets bpage->check_index_page_at_flush…
Post: Percona Server 5.1.58-12.9
August 17, 2011 By 2 Comments
… compressed tables and has been removed (buf_LRU_insert_zip_clean). Bugs Fixed: #802825 / #61341 in MySQL (Yasufumi Kinoshita). Compatibility Collations Two… in MySQL 5.1.24. If the following collations were used: utf8_general_ci ucs2_general_ci and any of the indexes… upgrading from 5.0 / 5.1.23 or lower: any indexes on columns in that situation must be rebuilt after the…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
… to find the different indexes and types. mysql (information_schema) > select i.INDEX_ID, i.NAME FROM INNODB_SYS_INDEXES as i INNER JOIN…’; +———-+———+ | INDEX_ID | NAME | +———-+———+ | 26 | PRIMARY | | 27 | emp_no | +———-+———+ B) InnoDB Table Monitor The index information can also be taken directly from MySQL using… key. After identifying the Primary Key don’t forget to remove the innodb_table_monitor. C) Check the size on disk…
