…row in set (0.00 sec) In previous versions of MySQL, you can create two indexes …redundancies could be necessary, depending on the query optimizer and the MySQL version, we always …the clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY (`id`), # Column types: # `col2` int…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
…general rule, because there are different types of subqueries (correlated, non-correlated, derived …table to store an interim result set, and then use that to …force MySQL to scan the `title` table first, grouping by kind_id in index … as the Derived-Table Solution on slides 55-59. Apologies if that…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
…compared MySQL 5.5.30 to MySQL 5.6.10, both with default settings … regions. The flights represent the type of investigations and drill-downs …Test environment These tests were done on a relatively fast machine with a…. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…ON Frm file corrupt, zeroed out, lost or SHOW CREATE TABLE crashes MySQL…indexes of the table. mysql> select * from SYS_INDEXES WHERE TABLE_ID=741; +———-+——+———————+———-+——+——-+———+ | TABLE_ID | ID | NAME | N_FIELDS | TYPE…(45) CHARACTER SET ‘utf8′ COLLATE ‘…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and tables sbtest$I_…mysql_table_engine=tokudb –oltp_auto_inc=on –max-time=18000 –report-interval=10 –max-requests=0 –num-threads=32 –rand-type… understand from documentation TokuDB comes with good settings out-of-box, but I am ready…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… behind index merge is that for certain types of queries which contain WHERE clauses with columns that had single-column indexes on them, MySQL could… see below) scan the indexes on indexed_colA and indexed_colB and then do a set-theoretic union of the two result sets. Replace that “OR…
Post: Do you always need index on WHERE column ?
August 28, 2007 By 17 Comments
…0; 1 row in set (10.43 sec) So only in the last case we really need the index on column `has_something`
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…on MySQL Sandbox to set up a little throw-away MySQL instance — that took ten seconds. And then I set… 70.0000 | | 1262633894049058504 | mpb_wordpress.wp_posts.type_status_date | 2 | 15 | 30.0000 | …index, 70% of the time in fact. The next variation is in row 2, the same query, which uses an index on…
Post: Troubleshooting MySQL Upgrade Performance Regressions
February 15, 2012 By Leave a Comment
… sure changing only MySQL version on the same hardware with same settings causes regression. … workload consisting of several query types. Though it is rather rare …INDEXES FROM for tables involved and check cardinality) Different stats can often cause different plans. Run ANALYZE TABLE on both MySQL…
Post: MySQL Upgrade Webinar Questions Followup
June 28, 2012 By 7 Comments
… order and data type related changes, so you still need to run mysql_upgrade to check … using Percona Server with enabled expand_fast_index_creation feature, as I just wrote this …on tables to be created as MyISAM by default. Regarding setting character set to utf8 – this is not needed as MySQL…
