…if we force the optimizer to use the index? mysql> EXPLAIN SELECT * FROM employees FORCE INDEX(idx_first) ORDER BY first_name\G *************************** 1. row *************************** id…much slower than sequential reads is correct, but it is not the case anymore if data is stored in memory. …
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
… using indexed column b in where clause: mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 555 ORDER BY…’ll try to use two separate queries: mysql> SELECT SQL_NO_CACHE * FROM count_test WHERE b = 666 ORDER BY c LIMIT 5… even when this was not needed (after the first 5 rows specified in LIMIT clause). With count(*) it used index scan inly which…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… looked at the “ORDER BY OPTIMIZATION” section in MySQL manual but could not find a reason why MySQL does not use index in the following scenario: CREATE TABLE table1 ( id bigint(20) NOT NULL AUTO… to use an index but it does not. I just see this note: With EXPLAIN SELECT … ORDER BY, you can check whether MySQL can use indexes to…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
… strings are not stored in the index even if it is not packed. Compressed blocks need to be treated differently. For uncompressed index blocks….id – same but using id column select c from t1 where c like “%abc%” order by c limit 1 – forward index scan select c from t1 where c like “%abc%” order by c desc limit 1 – reverse index scan…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…index MySQL will dive into the index to find first matching row and when will continue scanning index in order…not know of the hint in MySQL which would allow forcing using index merge when MySQL does not think it should be used…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
…use indexes, such as: Index merge improvements. But I expect that defining the right compound index will still be superior. Index…index exists for a query with both a GROUP BY and an ORDER BY clause? Right; you’re stuck, unless your query groups by…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
… | NULL | NULL | 1000000 | Using temporary | +—-+————-+——-+——+—————+——+———+——+———+—————–+ 1 row in set (0.01 sec) As you can see I’m using ORDER BY NULL clause as otherwise MySQL will sort the data after performing group by which is overhead we’re not looking… key_buffer_size which is not large enough to fit all key blocks from temporary table index, so we get a lot…
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
… 00:00:00′ AND ’2006-12-02 23:59:59′) ORDER BY published desc LIMIT 0,10 \G *************************** 1. row *************************** id: 1… 00:00:00′ AND ’2006-12-02 23:59:59′) ORDER BY published desc LIMIT 0,10 \G *************************** 1. row *************************** id: 1… often have trouble with (just do not have example today) is using different index, for example, having indexes (A) and (A,B) for query…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… rule of thumb I sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it’s generally applicable to any database server with b-tree indexes. And there…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…? Also note many simple “queries” (using constants for index accesses) will not use index cardinality data at all but…which use these stats accuracy at the order of magnitude is enough. Sometimes it is not and… and MyISAM have different stats computation method by default. Lets check how stats change …
