QOT

MySQL DBA’s intelligent assistant

Selectivity threshold for a non-covering index

April 29th, 2009 at 10:42am |

Assume you have a table with about 300 000 rows, and an indexed column ‘col1′ with only 9 distinct values. Now you got a query like ’select * from t1 where col1 = const’. The questions are

- when the index is faster to full table scan and vice versa?
- does MySQL use the optimal plan by default?

Read the rest of Selectivity threshold for a non-covering index

Redundant “Using where”

April 18th, 2009 at 6:37pm |

Working on server-based index analysis I once again recalled a topic I wanted to write about for many times. The topic is redundant “Using where” in query plans. Suppose we have a table like this:
mysql> show create table t1 \G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` int(11) NOT NULL DEFAULT ‘0′,
`b` int(11) […]

Read the rest of Redundant “Using where”