Hi,
Given this table:
create table occurrance (
word int unsigned not null,
document_id int unsigned not null,
offset mediumint unsigned not null,
key index_word (word),
key index_document_id (document_id),
key index_offset (offset)
) TYPE=MyISAM
..the following query takes way too long time (~10 minutes with about
1 million rows):
SELECT t0.document_id
FROM occurance t0,
occurance t1
WHERE t0.word=3649701880 and
t1.word=1821517919 and
t0.document_id=t1.document_id;
There's 2185 rows with the word value 3649701880, and 645 rows with
the word value 1821517919. It seems to me that the mysql server does
the very expensive t0.document_id=t1.document_id join first, and only
after that does the more restrictive constant value joins.
EXPLAIN on the select query above:
+-------+------+------------------------------+-------------------+
| table | type | possible_keys | key |
+-------+------+------------------------------+-------------------+
| t1 | ref | index_word,index_document_id | index_word |
| t0 | ref | index_word,index_document_id | index_document_id |
+-------+------+------------------------------+-------------------+
(continued)
---------+----------------+------+------------+
key_len | ref | rows | Extra |
---------+----------------+------+------------+
4 | const | 619 | where used |
4 | t1.document_id | 12 | where used |
---------+----------------+------+------------+
I'm not sure I'm reading this correctly, but it seems to me that it
uses the correct indices.
Any clues to why this query is so slow? Is there some way I can force
the join optimizer to do the both constant joins first, and *then*
the t0.document_id=t1.document_id clause?
(MySQL 3.23.32 on RH6.2)
// Johan
--
Johan Schön http://johan.schon.org
Roxen Internet Software http://roxen.com
---------------------------------------------------------------------
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
