Re: Partial key

[Ulf Wendel]

Nico,

an index access does not need to be the best possible access method. MySQL and MaxDB are using a cost based optimizer. An optimizer is responsible for calculating the best possible access method. Both optimizers are using some simple statistics plus B-tree evaluations to calculate the "costs" of an access method.

Evaluation means that they do a prefetch of some data to check if it makes sense to use an index of not. Except for MyISAM secondary indexes are non-clustered and thus it does not always make sense to use them because of jumping between the index tree and the data tree.

Please consult google on:

- B-trees
- clustered indexes
- non-clustered indexes
- cost based optimizers

> the key is a multiple index on name, departuredate
> and departuretime.
> the name of the key is name (this doesn't seem the
> problem).
> the tablename is A.
> the table has now 12 rows.
> 10 rows with name starting with an N:
> Noordman
> Noorman
> Noorman
> Naarman
> Noordman
> Noordman
> Noorman
> Naarman
> Noorman
> Noordman.
>
> Explain select * form A where name like 'N'%' says
> that the possible key is name, but it doesn't use
> the index. In the execution of the select the rows
> are showed in the range they were inserted.

That all makes perfectly sense. MySQL is behaves in this case like every other database system. Using an index for a table with 12 rows and likely a size of one page (16kb for InnoDB, 8kb for MaxDB) on disk is not faster in any way! A page is the smallest I/O unit of a database (MaxDB: 8 pages, 64kB by default). Generally speaking I/O operations are to be avoided in a database system because they are among the slowest operations at all.

MySQL will have to read one page to fetch the contents of the entire table in our 12 row case . That's one I/O operation.

If it would use an index, then it would have to fetch one page that contains the index and one page page contains the entire table. That's two I/O operations.

So, why should MySQL choose an index strategy? The index strategy might make sense if it's an index only acces is possible, but that's another story.

Add data to your table and you'll see that MySQL starts to use indexes if a suitable index is available.


> What I want is that in all cases the index is
> used.

What you want will slwo things down. Check the manual for notes how to enforce index usage. But this will slow down the query. Not notably for 12 records, but it will...

Ulf



Edited 1 time(s). Last edit at 06/17/2005 02:15AM by Ulf Wendel.