… about packed keys and performance implications it causes. First lets see how key compression works. Key compression applies on the block boundaries – first index value…) NOT NULL default ”, KEY `c` (`c`), KEY `id` (`id`) ) ENGINE=MyISAM Index size: PACK_KEYS=DEFAULT – 1550K PACK_KEYS=1 – 1453K PACK_KEYS=0 – 8176K As… is almost 3 times faster. With PACK_KEYS=1 we got join by integer key performing almost 5 times slower than it was…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… does not fit in the buffer pool, fast index creation provides even better performance as compared to the default method, because it…_fast_index_creation is OFF to avoid dropping keys that are part of a FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys because…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use index…select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | …
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… – index entries being sorted by primary key for each complete key value. So when you have index (a) and id is a primary key the real index is (a,id) when we extend index…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
… lookup by indexes by passing BTREE index and using hash, which is faster MyISAM does compression for character keys which makes it perform slower… cases situation is very different and MyISAM compression for char keys could frequently positevely impact performance.
Post: Why Index could refuse to work ?
September 8, 2006 By 15 Comments
… force index (PRIMARY) where article_id=10; +—-+————-+———+——+—————+——+———+——+——-+————-+ | id | select_type | table | type | possible_keys | key | key_len |… index in this case, simply by converting number to the string and performing index…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… do I read these results ? CHAR keys are indeed slower for joins compared to integer keys Performance degradation can range from few percent to couple of times for Innodb tables MyISAM Tables may suffer significantly if key… bound workload results are likely to be different as longer indexes expected to have much worse cache fit, especially if you…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… foreign keys are still handled on storage engine level making them not working for partitioned tables as well as making foreign keys performed row by row which often can be very inefficient. As results of Foreign Key limitations you might… satisfy foreign key conditions (have column child table is referencing and have it of proper type and indexed): mysql> set foreign_key_checks…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… queries. This will use fast primary key for some queries and use key K as covering index for lookup in other direction. For… of indexes I prefer to add indexes only in cases which provides positive impact to performance. At some point the gains from indexes you add will be less than performance loss because of having too many indexes. Q…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
…create table t; [...] PRIMARY KEY (`i`), KEY `key_name` (`name`,`i`) In this example the index ‘key_name’ includes the primary key so that last column…the problem. Conclusion Indexes are good for our queries but too many indexes or duplicate or redundant indexes can hurt the performance. Checking the…
