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?
-Andrei
"When I get a little money, I buy books;
and if any is left I buy food and clothes." -- Erasmus
---------------------------------------------------------------------
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
