June 19, 2013

Post: MySQL Query Patterns, Optimized - Webinar questions followup

June 3, 2013 By 2 Comments

On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars.  If…multiple columns in a compound index, then the order would matter, and it would have to match the order of columns in the index…` by a secondary index.  But the result was that it created a temporary …

Filed Under: Insight for Developers, MySQL, MySQL Webinars Tagged With: , , ,

Post: Multiple column index vs multiple indexes

August 22, 2008 By 13 Comments

…multiple column index would be faster but I also went ahead to do some benchmarks today. I’m using couple of simple tables: CREATE…Multiple Column index beats Index Merge in all cases when such index can be used. It is also worth to watchout a MySQL…

Filed Under: Benchmarks, Insight for DBAs

Post: The Optimization That (Often) Isn't: Index Merge Intersection

December 14, 2012 By 6 Comments

…columns that had single-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… recently that did indeed attempt to use index_merge, but with disappointing results. CREATE TABLE users ( user_id INT UNSIGNED …

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5

April 4, 2012 By 4 Comments

…you could be reading the same page multiple times into the buffer pool. So considering…index range lookups. This means MySQL can employ many other optimizations like for example if columns other then the secondary key columns… a hash table will be created based on the columns of table t1 that are …

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: Quickly finding unused indexes (and estimating their size)

December 5, 2012 By 2 Comments

…indexes; mysql> create view droppable_indexes as select all_indexes.table_schema as table_schema, all_indexes.table_name as table_name, all_indexes.index_name as index…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

… on the same column This is the easiest one. You can create multiple indexes on the same column and MySQL won’t complain. Let’s see this example: mysql> alter table t add index(name); mysql> alter table t add index…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: Multi Column indexes vs Index Merge

September 19, 2009 By 31 Comments

…=’CA’ they would create 2 separate indexes on AGE and STATE columns. The better strategy is often to have combined multi-column index on (AGE,STATE). Lets… than multiple column indexes. This is in case you’re using OR between the columns. In this case the combined index is useless and MySQL has…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… full-text index on it, and then I create one, the following warning is generated: mysql> alter table…you create a column called FTS_DOC_ID which is a BIGINT UNSIGNED NOT NULL with a unique index on…re on the subject of adding and dropping FT indexes, it’s entirely possible to DROP multiple FT indexes…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Duplicate indexes and redundant indexes

August 17, 2006 By 38 Comments

… having multiple keys on same column(s) – I guess someone thought key would make sense while did not notice it was already created. MySQL is…created on the same column(s) – perfect example is BTREE index and FULLTEXT index, while other combinations may also make sense. Note: Order of columns in index…

Filed Under: Insight for DBAs Tagged With:

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…column named “value”. OK, sounds easy enough: mysql: SHOW CREATE TABLE innodb_myisam_stopword\G *************************** 1. row *************************** Table: innodb_myisam_stopword Create Table: CREATE… set. mysql: CREATE TABLE innodb_… FT index on the… get back multiple matches containing the…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,
« Older Posts