… MySQL 5.6? Let’s have a look at the code implementing this feature. The warning will only be thrown if the index… MySQL, for which we at Percona tend to find redundant indexes. And second, because in some cases, we may need to have what at… 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
… a general rule, because there are different types of subqueries (correlated, non-correlated, derived tables, scalar… name (k1, k2, k3) is an index lookup. Look at the EXPLAIN output — it accesses these first…case to force MySQL to scan the `title` table first, grouping by kind_id in index order. …
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 …of data at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (…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
…:100M:autoextend innodb_flush_log_at_trx_commit = 2 innodb_flush_method…type=uniform –forced-shutdown=1 –max-time=120 –max-requests=0 –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql…read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT…
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
…to BUG#67808. For MySQL 5.6.11 top mutexes is &buf_pool->mutex,&new_index->lock. I profiled 5…2000M innodb_log_files_in_group = 2 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size=128M …definition-cache=1000 query_cache_size=0 query_cache_type=0 performance_schema=0 #56only loose-innodb_flush_…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… indexes of the table. mysql> select * from SYS_INDEXES WHERE TABLE_ID=741; +———-+——+———————+———-+——+——-+———+ | TABLE_ID | ID | NAME | N_FIELDS | TYPE | SPACE…form the primary key. The matter is regardless at what position primary key fields are defined in…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and tables…mysql_table_engine=tokudb –oltp_auto_inc=on –max-time=18000 –report-interval=10 –max-requests=0 –num-threads=32 –rand-type…innodb_log_buffer_size = 256M innodb_flush_log_at_trx_commit = 1 innodb_buffer_pool_size =…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…_type,status,parent_id); Using where; Using index; Using filesort At first glance, this might not look too bad. MySQL is using three different indexes… user type. When we think about what this means in the context of index merge, it means that MySQL is really looking at a… know that it’s the index on (user_type) that is the one that MySQL should be looking at, we can rewrite the query…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… set. This is most typical reason index gets added on the first place. Most popular index type in MySQL – BTREE can speed up equality and prefix range matches. So if you have index on (A,B) This index can… MySQL uses single index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index merge” Using Index…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
…_3` (`name`) [...] MySQL detects that the ‘name’ index already exists so it creates the new one appending a number at the end of the name. This type of indexes are easy to find out and to avoid them. How? Just specify the index name and MySQL will avoid…
