… wrote one week ago about how to find duplicate indexes. This time we’ll learn how to find unused indexes to continue improving our schema …going to use the benchmark software tpcc-mysql. After some runs we can start to analyze the information on INDEX_STATISTICS table. mysql> SELECT * FROM INDEX_…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
…indexes are easy to find out and to avoid them. How? Just specify the index name and MySQL will avoid to create duplicate indexes: mysql> alter table t add index…redundant index is on the ‘name’ column. To take benefit from a composite index MySQL doesn’t need to use all the columns of that index, …
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
… updating the index can become quite complex. Solr is easier to add documents to, either individually as you add new data to the MySQL database or else periodically batch-insert data that has changed since last time you updated the index. You can use the DataImportHandler to…. There are books that describe how to use one technology or the other, and some may compare one technology to the other, but typically…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…index lookups. So we spoke about how MySQL uses the index but not exactly what it gets from the index – typically (unless it is covering index) MySQL…
Post: Explaining Indexes with a Library Metaphor
August 30, 2011 By 1 Comment
… and is similar to how a database looks at blocks on the hard disk when its not using an index. Using an index You are interested… have saved yourself from walking over to the shelves. Using a covering index You are interested to know how many pages a certain book by J.R. Hartley has. You go over to the index…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… fact about MySQL Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use index and how it is going to do…. So MySQL Will not use indexes in all cases when it is technically possible. For multiple key part indexes MySQL will only be able to use multiple…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…=”attachment_7393″ align=”alignright” width=”300″] Catch the webinar: “Learn How MySQL 5.6 Makes Query Optimization Easier” for more tips on…: const rows: 10259274 Extra: Using where; Using index 1 row in set (0.00 sec) I reported this issue to Oracle and they confirmed… more ways to make a query faster, which means you need more manual care and tuning now. MySQL is conservative about “Using index” -in…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…. The index lookup is being perform but as only actual rows which are found and returned up to the top level MySQL part… access type to the tables is “const” MySQL does not count it as access to two tables. In case of “real” access to the… but rather helps to understand how much optimization is possible. For example I might have query which uses some index scans 1000 rows and…
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
…status | non-Primary | | wsrep_connected | ON | | wsrep_local_index | 0 | | wsrep_provider_name | Galera | | wsrep_provider…to use the mysql server: [root@percona1 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL… the right thing, this is how it needs to work and you don’t…
Post: Finding your MySQL High-Availability solution – The questions
October 16, 2009 By 36 Comments
…, let’s move on! How to determine which MySQL High-Availability solution is best? What is really tricky with MySQL is the number of… handles GIS indexes. HA solutions Need MyISAM Full text or GIS indexes “MySQL replication” and “Master-Master with MMM manager” Don’t use any special MyISAM feature All Can change MyISAM Full text to…
