…) optimizer issue: a “SELECT *” taking half the time to execute than the same “SELECT one_indexed_column” query in MySQL 5.6.10. This turned into a really nice exercise for checking the performance… surprising outcome: mysql> SELECT * FROM cast_info WHERE role_id = 1 and note like ‘%Jaime%’; On a table like this: CREATE TABLE `cast_info…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…index_usage \ –create-save-results-database After that finishes, the ‘index_usage’ database contains several tables: mysql> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes | | queries | | tables…
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
…For multiple key part indexes MySQL will only be able to use multiple keyparts if first keyparts matched with “=”. Here is example: mysql> explain SELECT…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…mysql> explain select * from sbtest a,sbtest b where a.id=5 and b.id=a.k; +—-+————-+——-+——-+—————+———+———+——-+——+——-+ | id | select_type | table…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
…the fact MySQL prefers when it is possible to use index for further restriction and than using file sort, rather than using index for …price desc limit 10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: goods type: ref possible_keys: cat_id key: …
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…for Table Cache Related Allocations There are cases when MySQL will allocate a lot of memory for table…tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | …
Post: Talking MySQL to Sphinx
April 19, 2009 By 4 Comments
…for help. Type ‘\c’ to clear the buffer. For the tests I used the table…indexes there defined – Sphinx does not support explicit indexes and it is clear when you can use index for sort MySQL… 10 rows in set (32.47 sec) MySQL mysql> select max(forum_id…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
…. How? Just specify the index name and MySQL will avoid to create duplicate indexes: mysql> alter table t add index key_for_first_name(name); Query OK, 0 rows affected (0.01 sec) mysql> alter table t add index key_for… redundant key can be useful. SELECT * FROM t WHERE name=’kahxailo’ AND i > 100000; With a index on (name) the execution plan…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… differently? We can “compress” the table in the database by removing the duplicates: mysql> create table ex2 as select val, count(*) from ex1 group… to be decompressed. Third, the COUNT acts as RLE compression for the other attributes. Don’t use FLOAT with this method… is no index on this table. mysql> delete from data where val=16; Query OK, 1 row affected (3.14 sec) mysql> select val, count…
Post: Data compression in InnoDB for text and blob fields
May 30, 2012 By 2 Comments
…t1` WHERE `id` < 1000 compare this to: SELECT AVG(LENGTH(COMPRESS(`colTextField`))) FROM `t1` WHERE …index page). Based on our analysis 75% of the blobs stored in the table were over 8KB, which were responsible for…As the fine MySQL manual states: This setting may still be useful for tables with many…
