… index creation (merge sort). For the MyISAM tests I used a 10GB key buffer. I used ALTER TABLE DISABLE KEYS and built the keys with sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
…Can you compare the use of subqueries/multiple joins vs. multiple queries (e.g. temp tables)? For performance,… a suboptimal form. Q: Doesn’t the primary key solution for random selection only work when the… case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… of course I wanted to compare InnoDB performance vs TokuDB. I have a particular workload I’…NULL, pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and tables…myisam_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside single-statement… on a single-node (on the left) vs. two-node (on the right) configuration. No … –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + …
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… single column BTREE index MySQL will dive into the index to find first matching row and when will continue scanning index in order until… spoke about how MySQL uses the index but not exactly what it gets from the index – typically (unless it is covering index) MySQL gets a “row pointer” which can be primary key value (for Innodb…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 4 Comments
… that we have traded many point index lookups to one or more index range lookups. This means MySQL can employ many other optimizations like for example if columns other then the secondary key columns… query time is 48.78s vs 48.91s, so I don’t see Hash Join and Key-ordered Scan making much of…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
… idx_first index, which will translate to a full index scan. Let’s see how the optimizer will execute it: mysql> EXPLAIN SELECT…_first index as a relevant choice (possible_keys: NULL). What do we get if we force the optimizer to use the index? mysql> EXPLAIN SELECT * FROM employees FORCE INDEX(idx…
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
… tried to create an InnoDB FT index before declaring the `FTS_DOC_ID` primary key column properly, MySQL crashed with an out of… to the MySQL database or else periodically batch-insert data that has changed since last time you updated the index. You can… where I announced the webinar: We evaluated MySQL vs Solr. I was able to index all the data from the database into Solr…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…part of analyzing surprising MySQL 5.5 vs MySQL 5.6 performance results I’ve been looking …index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on 1 row in set (0.00 sec) mysql…=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…vs range scan by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql…or require radom IO if index ranges are scanned. There are also clustered keys in Innodb which combine index access with data …
