June 18, 2013

Post: Extending Index for Innodb tables can hurt performance in a surprising way

May 22, 2010 By 10 Comments

…select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+———-+——+—————+——+———+——-+——+——————————————+ | 1 | SIMPLE | idxitest | ref | a | a | 4 | const | 1 | Using where; Using index; Using…

Filed Under: Insight for DBAs, MySQL Tagged With: ,

Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact

March 12, 2012 By 12 Comments

… it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , ,

Post: Using Multiple Key Caches for MyISAM Scalability

November 26, 2008 By 8 Comments

I have written before – MyISAM Does Not Scale, or it does quite well – two main things stopping you is table locks … can use traditional TABLES table: mysql> select table_schema,table_name,index_length from information_schema.tables where table_schema=’test’ and table_name in…

Filed Under: Insight for DBAs Tagged With:

Post: Covering index and prefix indexes

November 23, 2006 By 5 Comments

… covering index if you do not touch columns which only have prefixes in the index. Notice “Using Index” in Extra column. mysql> explain select j,k from t where… in the index but MySQL does not look at the actual data in this case it only looks at definitions. mysql> explain select k from t where…

Filed Under: Insight for DBAs Tagged With:

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…does not…indexes, it’s entirely possible to DROP multiple FT indexes with InnoDB in the same ALTER TABLE statement, but it’s not…use_stopword | 1 | | table_state | 0 | +—————————+——–+ 10 rows in set (0.00 sec) mysql> insert into dir_test_innodb (full_name, details) SELECT…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Getting MySQL to use full key length

January 31, 2007 By 5 Comments

… TABLE does not help, at least in this case. However looks like as one of its side effects FORCE INDEX actually forces index to be used to largest extent possible: mysql> explain SELECT thread_id FROM nn2_msg132.msg132 force index(group_id) WHERE…

Filed Under: Insight for DBAs Tagged With: ,

Post: Quickly finding unused indexes (and estimating their size)

December 5, 2012 By 2 Comments

…indexes in all_indexes, but not in used_indexes.  These indexes (if our original index statistics are good) are candidates to be dropped: create view droppable_indexes as select all_indexes…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5

April 4, 2012 By 4 Comments

…index lookups are performed on table2. Of course this could mean a lot of random lookups in case the dataset does not…selected rows from t1 would be read and then for all rows, index lookup would be performed on t2. Here is where…size of the buffer that MRR uses appropriately. In my tests I noticed …

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: Why Index could refuse to work ?

September 8, 2006 By 15 Comments

…Using where | +—-+————-+———+——+—————+———+———+——-+——+————-+ 1 row in set (0.00 sec) Looks much better does not…

Filed Under: Insight for DBAs Tagged With:

Post: Analyzing air traffic performance with InfoBright and MonetDB

October 2, 2009 By 50 Comments

…in sense count of direct connected cities for different diapason of years. SELECT DestCityName, COUNT( DISTINCT OriginCityName) FROM ontime WHERE…not able to edit query or check history), the documentation also needs improvements. The fact it does not use…

Filed Under: Benchmarks, MySQL Tagged With: , , ,
« Older Posts
Newer Posts »