Andrei Zmievski wrote:
>
> Just installed 3.23.29-gamma and played around with fulltext indexes and
> some other stuff. Ran into a weird problem that I hope you can shed some
> light on.
>
> Basically, a query's where clause has two parts joined by 'and':
>
> select count(*) from ARTICLES where Publication_ID = 9 and Status =
>'completed';
>
> It takes 10 seconds to run in a table of 10,000 records. If I do a
> query using those parts separately, the results come back instantly. I
> ran explain on them and here are the results:
>
> mysql> show keys from ARTICLES;
>
>+----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+----------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
>Collation | Cardinality | Sub_part | Packed | Comment |
>
>+----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+----------+
> | ARTICLES | 0 | PRIMARY | 1 | Article_ID | A
> | 10440 | NULL | NULL | |
> | ARTICLES | 1 | Publication_ID | 1 | Publication_ID | A
> | NULL | NULL | NULL | |
> | ARTICLES | 1 | Incoming_Datetime | 1 | Incoming_Datetime | A
> | NULL | NULL | NULL | |
> | ARTICLES | 1 | idx_search | 1 | Headline | A
> | NULL | 1 | NULL | FULLTEXT |
> | ARTICLES | 1 | idx_search | 2 | Tagline | A
> | NULL | 1 | NULL | FULLTEXT |
> | ARTICLES | 1 | idx_search | 3 | Byline | A
> | NULL | 1 | NULL | FULLTEXT |
> | ARTICLES | 1 | idx_search | 4 | Body | A
> | NULL | 1 | NULL | FULLTEXT |
> | ARTICLES | 1 | Status | 1 | Status | A
> | NULL | NULL | NULL | |
>
>+----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+----------+
> 8 rows in set (0.00 sec)
>
> mysql> explain select count(*) from ARTICLES where Status = 'completed';
>
>+----------+------+---------------+--------+---------+-------+------+-------------------------+
> | table | type | possible_keys | key | key_len | ref | rows | Extra
> |
>
>+----------+------+---------------+--------+---------+-------+------+-------------------------+
> | ARTICLES | ref | Status | Status | 255 | const | 8587 | where used;
>Using index |
>
>+----------+------+---------------+--------+---------+-------+------+-------------------------+
> 1 row in set (0.00 sec)
>
> mysql> explain select count(*) from ARTICLES where Publication_ID = 9;
>
>+----------+------+----------------+----------------+---------+-------+------+-------------------------+
> | table | type | possible_keys | key | key_len | ref | rows | Extra
> |
>
>+----------+------+----------------+----------------+---------+-------+------+-------------------------+
> | ARTICLES | ref | Publication_ID | Publication_ID | 4 | const | 8526 | where
>used; Using index |
>
>+----------+------+----------------+----------------+---------+-------+------+-------------------------+
> 1 row in set (0.00 sec)
>
> mysql> explain select count(*) from ARTICLES where Publication_ID = 9 and Status =
>'completed';
>
>+----------+------+-----------------------+------+---------+------+-------+------------+
> | table | type | possible_keys | key | key_len | ref | rows | Extra
> |
>
>+----------+------+-----------------------+------+---------+------+-------+------------+
> | ARTICLES | ALL | Publication_ID,Status | NULL | NULL | NULL | 10440 | where
>used |
>
>+----------+------+-----------------------+------+---------+------+-------+------------+
> 1 row in set (0.00 sec)
>
> Why isn't it using index on the last query?
>
Because there isn't an index that contains both fields.
---------------------------------------------------------------------
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
