… would merge the delta index with the main index periodically. Sphinx Search also supports a supplementary in-memory RT index type that supports direct row-by-row updates. But you would still have to update the RT index as data… where I announced the webinar: We evaluated MySQL vs Solr. I was able to index all the data from the database into…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…of one of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we have …like ‘%Jaime%’\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: cast_info type: ref possible_keys: role_id_note key: …
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…MySQL Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use index…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
… and MySQL won’t complain. Let’s see this example: mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> show create… name. This type of indexes are easy to find out and to avoid them. How? Just specify the index name and MySQL will avoid…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…of unused indexes: mysql> SELECT INNODB_INDEX_STATS.TABLE_NAME, INNODB_INDEX_STATS.INDEX_NAME from INNODB_INDEX_STATS WHERE CONCAT(INNODB_INDEX_STATS.index_name, INNODB_INDEX_STATS…unique ALTER TABLE `tpcc`.`stock` DROP KEY `fkey_stock_2`; — type:non-unique If you compare these results with the …
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 | | queries …70.0000 | | 1262633894049058504 | mpb_wordpress.wp_posts.type_status_date | 2 | 15 | 30.0000 |…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… | | TABLE_DEFINITION_CACHE | 400 | 1400 | | PORT | 5530 | 5610 | | QUERY_CACHE_TYPE | ON | OFF | | REPORT_PORT | 5530 | 5610 | | PERFORMANCE_SCHEMA_MAX_FILE…=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on 1 row in set (0.00 sec) mysql [localhost…_materialization_cost_based=on,use_index_extensions=on 1 row in set (0.00 sec) Summary: MySQL 5.6 comes with quite…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… constantly referring to the amazing MySQL manual, especially the option and variable reference table. But …slave_skip_errorsblogpercona.commanual slave_transaction_retriesblogpercona.commanual slave_type_conversionsblogpercona.commanual Slow_launch_threadsblogpercona.commanual slow_launch_…
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… and b.id=a.id; +—-+————-+——-+——–+—————+———+———+————-+——+——-+ | id | select_type | table | type | possible_keys | key | key_len | ref |…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… set. This is most typical reason index gets added on the first place. Most popular index type in MySQL – BTREE can speed up equality and prefix range matches. So if you have index on (A,B) This index can… such case you would see “Index” type in explain which correspond to scanning (potentially) complete table in the index order. It is very…
