Bug #430 Optimisation

• Submitted: 14 May 2003 5:41
• Modified: 14 May 2003 15:29
• Reporter: Henryk Szal
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 3.23.52-max-debug-log
• OS: Windows (Windows)
• CPU Architecture: Any

[14 May 2003 5:41] Henryk Szal

Description:
Using index for query optimisation. Scenario:

1. Create table

create table x (
	a smallint, 
	b smallint, 
	c integer, 
	d float,
	f float
) type = innodb;

create unique index x1 on x (a, b, c);

2. Insert some (10000) records to x

3. Explain two similar queries:
explain select * from x where a <= CONST and b >= CONST;
and 
explain select * from x where a >= CONST and b <= CONST;

Explain shows that first query use index to speed-up query, but second one not.
Why? I read in the manual that in this kind of query all relational operators (<,<=,=,>,>=) are optimised and engine use index to speed-up query.

How to repeat:

Suggested fix:
?

[14 May 2003 15:29] Michael Widenius

MySQL can use an index on the 'a' column in both the given cases.
MySLQ may however prefer to do a table scan if the given range matches too many rows, as a table scan is quite fast.  This is described in more detail in the MySQL manual.

Note that MySQL can only use index efficiently for queries where MySQL can create a range for the index.  If you have

A < CONST and B > CONST

MySQL can solve the query by serching from the key (A,B) forward until the end of file.

If you have A < CONST and B > CONST

the MySQL can solve the query by searching between keys
(NULL, B) -> (A,NULL)