* Andrei Zmievski <[EMAIL PROTECTED]> wrote on 23.01.01 16:54:
> mysql> show keys from ARTICLES;
>... [Andrei's mail trimmed down quite a bit]:
> | ARTICLES | 1 | Publication_ID | 1 | Publication_ID | A
> | NULL | NULL | NULL | |
>...
> | ARTICLES | 1 | Status | 1 | Status | A
> | NULL | NULL | NULL | |
>...
> mysql> explain select count(*) from ARTICLES where Status = 'completed';
>...
> | ARTICLES | ref | Status | Status | 255 | const | 8587 | where used;
>Using index |
>...
> mysql> explain select count(*) from ARTICLES where Publication_ID = 9;
>...
> | ARTICLES | ref | Publication_ID | Publication_ID | 4 | const | 8526 | where
>used; Using index |
>...
> mysql> explain select count(*) from ARTICLES where Publication_ID = 9 and Status =
>'completed';
>...
> | ARTICLES | ALL | Publication_ID,Status | NULL | NULL | NULL | 10440 | where
>used |
>...
> Why isn't it using index on the last query?
MySQL shows "Using index" in the first two queries because it could find the
whole result in one of the indexes in these cases (it only needed to look at
the index and not the table).
With your last query, there is no index which would contain both
Publication_ID and Status in the first two key fields, and thus MySQL must
also refer to the table.
Cardinality probably also plays an issue here, but I'll leave describing
that up to some more knowledgeable people (I can't remember that offhand and
I do need to go and eat now).
--
Tomi Junnila <[EMAIL PROTECTED]>
http://www.badzilla.net/~topeju/
Electronics and Information Technology,
University of Turku, Finland
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
