… something like this in MySQL 5.5, you will succeed without errors or warnings: mysql> ALTER TABLE test ADD INDEX (col2); Query OK… the index will be created-, but we will get a warning (note severity, to be exact): mysql> ALTER TABLE test ADD INDEX (col2… a prefix of the clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY (`id`), # Column types: # `col2` int(11) default null…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… hard to make a general rule, because there are different types of subqueries (correlated, non-correlated, derived tables, scalar subqueries) and… the tables. It seemed to prefer an index-scan of 7 rows in the `kind_type` table to be first, then look… in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This made the…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
…or different regions. The flights represent the type of investigations and drill-downs that …filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For…mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…-type=uniform –forced-shutdown=1 –max-time=120 –max-requests=0 –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket…-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + UPDATE QPS test…
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
… related to BUG#67808. For MySQL 5.6.11 top mutexes is &buf_pool->mutex,&new_index->lock. I profiled 5.6… table-definition-cache=1000 query_cache_size=0 query_cache_type=0 performance_schema=0 #56only loose-innodb_flush_neighbors=0…_instances=16 # MySQL 5.5 and 5.6 loose-innodb_io_capacity_max = 15000 #Percona only innodb_adaptive_hash_index_partitions=8…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… id to find indexes of the table. mysql> select * from SYS_TABLES WHERE NAME=’sakila/actor’; +————–+—–+——–+——+——–+———+————–+——-+ | NAME | ID | N_COLS | TYPE | MIX_ID…` among the indexes of the table. mysql> select * from SYS_INDEXES WHERE TABLE_ID=741; +———-+——+———————+———-+——+——-+———+ | TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE | PAGE…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…, pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and tables sbtest$I_r10… –mysql_table_engine=tokudb –oltp_auto_inc=on –max-time=18000 –report-interval=10 –max-requests=0 –num-threads=32 –rand-type… = 10G myisam_repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-related options are…
Comment: State of the art: Galera - synchronous replication for InnoDB
April 23, 2013 By 15 Comments
… write 100,000 queries in about 50-60 seconds to mysql 5.1 and in about 6-9 seconds on mariadb…-cols=6 –number-char-cols=6 –auto-generate-sql-secondary-indexes=3 –auto-generate-sql –csv=/tmp/mysqlslap_3index_innodb.csv…-generate-sql-load-type=mixed –number-of-queries=100000 MariaDB-Galera-server-5.5.29-1.x86_64 mysql-5.1.67…
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… MySQL is doing this; run an EXPLAIN on your SELECT and you’ll see “index_merge” as the type of query and the type… BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user_type) WHERE user_type=2 AND user_id > 2938575…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… multi-column index on (AGE,STATE). Lets see why it is the case. MySQL indexes are (with few exceptions) BTREE indexes – this index type is very… index) mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest ignore index (combined) where i1=50 and i2=50; +—-+————-+———+————-+—————+——-+———+——+——+————————————-+ | id | select_type…
