… of index. Only last one is allowed to have “range” clauses, such as >, IN etc. All clauses which follow the range in the index will not use index for their operation….S If you post queries in your comments please also explain which indexes do you have on the table. SHOW CREATE TABLE…
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… 1 row in set (0.00 sec) mysql> explain select * from goods force index(cat_id) where cat_id=5 and seller… rows to display only few. If we force index as in second query explain will look scary with estimated million of rows… to better take into account column selectivity together with LIMIT range. If there are only few values for given seller_id…
Post: Explaining Indexes with a Library Metaphor
August 30, 2011 By 1 Comment
… explaining indexes is comparing them to index cards in an old library. In an old library, you used to (or still do) have index… that this step would be much faster. Using an index with range scans You would like to count how many books J.R. Hartley has ever written. You goto the index cards, look…
Comment: 3 ways MySQL uses indexes
July 20, 2010 By 28 Comments
… MySQL runs into the interval range it will not use any further index parts.” WRONG! Triple range lookup: EXPLAIN SELECT * FROM tree WHERE 1… range lookups with speeds in miliseconds. It’s maybe difficult to make it work the first time, to make the correct index…
Post: Extended EXPLAIN
July 24, 2006 By 9 Comments
…rows | Extra | +—-+————-+——–+——-+—————+———+———+——+——–+————-+ | 1 | SIMPLE | sbtest | range | PRIMARY | …PRIMARY | 4 | func | 1 | Using index; Using where | …EXPLAIN EXTENDED is very valuable addition to EXPLAIN…
Comment: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
… work. The EXPLAIN should be like in the second case whether FORCE INDEX clause is present or not. The “use range(index) instead of ref(the-same-index)” heuristics has been in…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… further index key parts as soon as there is a range clause on the previous key part. So if you have INDEX(A… is used. Let us now replace the range with IN list in this query: mysql> explain select sum(events) from info where…: 30 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: info type: range possible_keys: d key: d…
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
… where | +—-+————-+————-+——-+—————+———–+———+——+——+————-+ 1 row in set (0.00 sec) EXPLAIN SELECT * FROM coordinates FORCE INDEX (x_y_col_a) WHERE x BETWEEN 30… a range like this prevents us from using the rest of the index. There is a workaround, but it’s not pretty: EXPLAIN… WHERE conditions (we are just no longer using an index on them): EXPLAIN SELECT * FROM coordinates WHERE x_floor in (30,31…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… range allows to optimize lookups on the second key part, while BETWEEN and other ranges do not. Using same access type in EXPLAIN… – we can see Handler_read_key=1 – this means one index range scan was initiated. Handler_read_next=42250 means 42250 rows… deal with ether as BETWEEN range or as IN filesort appears and query becomes very slow: mysql> explain select * from people where…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 45 Comments
… few more queries: mysql> select count(*) from fact where i explain select count(*) from fact where i select count(val) from fact where i explain select count(val) from fact where i select count(val2) from fact where i explain select count(val2) from fact where i alter table fact…
