… key constraint which implied an implicit secondary index with the same name. Fixed by detecting… there is a transaction that executed a query which opened that table. Bug fixed #1127008 (…fixed #1163262 (Alexey Kopytov). When mysqldump was used with –innodb-optimize-keys and –no-data options, all …
Post: Percona Server for MySQL 5.5.30-30.2 now available
April 10, 2013 By 10 Comments
… key constraint which implied an implicit secondary index with the same name. Fixed by detecting… there is a transaction that executed a query which opened that table. Bug fixed #1127008 (…fixed #1163262 (Alexey Kopytov). When mysqldump was used with –innodb-optimize-keys and –no-data options, all …
Comment: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… is indexes. If you add multiple multi-column indexes the difference with InnoDB will be even more notable. Also it allows multiple clustering indexes that give additional huge win on selects. We used TokuDB in the past (versions 3.x and 4.x). It really shines on large tables and index scan like queries.
Comment: Benchmarking Percona Server TokuDB vs InnoDB
May 8, 2013 By 32 Comments
… concurrency did you use for the INSERT test? Don’t know if TokuDB handle concurrent workload as well as InnoDB 2) Can you also run benchmark on SELECT by PK or some range query by a secondary index? I heard that TokuDB is slower than InnoDB on SELECT but it’d…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
…can use the indexes for query execution, which are not mutually exclusive, in fact some queries will use indexes for all 3 purposes listed here. Using index …some workarounds you can use though. Using index to read data Some storage engines (MyISAM and Innodb included) can also use index to read the …
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… fast index creation feature introduced in the InnoDB plugin is that it only works when indexes are explicitly created using ALTER TABLE or CREATE INDEX…, c FLOAT) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t(c) VALUES (RAND()); Query OK, 1 row…=InnoDB for InnoDB tables and thus, is just a special case of the previous one: mysql> SET expand_fast_index_creation=OFF; Query OK…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
… “queries” (using constants for index accesses) will not use index cardinality data at all but will estimate number of rows during query execution…innodb type=innodb; Query OK, 245760 rows affected, 1 warning (51.87 sec) Records: 245760 Duplicates: 0 Warnings: 0 mysql> show index from antest_innodb…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6… 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 cannot be used for filtering records. For…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…INNODB_INDEX_STATS.TABLE_NAME, INNODB_INDEX_STATS.INDEX_NAME from INNODB_INDEX_STATS WHERE CONCAT(INNODB_INDEX_STATS.index_name, INNODB_INDEX_STATS.table_name)NOT IN(SELECT CONCAT(index_statistics.index_name, index…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… different queries. This will use fast primary key for some queries and use key K as covering index for lookup in other direction. For Innodb Table… ORDER BY B will use index (A,B) for sorting optimization, for more complicated conditions you will need to use something like Trick… lot more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary…
