Hello everyone, I'm having a hard time with the following query. It retrieves about 3K rows from a few tables. One of them contains over 40M rows. When run on a 3Ghz server with 1G of RAM it returns the rows in more than 1 mini. I don't think that's normal.
Here's the output of EXPLAIN: mysql> explain SELECT ti.posi, ti.docid, d.filename, ti.id, c.name FROM corpus_documents cd, corpus c, documents d, tokens_ins ti, tokens t WHERE c.corpusid=4 AND cd.corpusid=c.corpusid AND cd.docid=d.docid AND t.docid=d.docid AND ti.id=t.id AND t.word='police' AND t.docid=ti.docid\g; He +-------+--------+--------------------------------------------------------------+---------------+---------+---------------+------+--------------------------+| table | type | possible_keys | key | key_len | ref | rows | Extra |+-------+--------+--------------------------------------------------------------+---------------+---------+---------------+------+--------------------------+| c | ref | PRIMARY,corpus_corpusid | PRIMARY | 3 | const | 1 | Using where; Using index || t | ref | PRIMARY,tokens_id,tokens_docid,tokens_word,tokens_word_docid | PRIMARY | 30 | const | 24 | Using where || ti | ref | PRIMARY,tokens_ins_id,tokens_ins_docid | tokens_ins_id | 4 | t.id | 96 | Using where || d | ref | PRIMARY,documents_docid | PRIMARY | 3 | t.docid | 3 | Using index || cd | eq_ref | PRIMARY | PRIMARY | 6 | const,d.docid | 1 | Using where; Using index |+-------+--------+--------------------------------------------------------------+---------------+---------+---------------+------+--------------------------+ 5 rows in set (0.00 sec) It seems to be using indexes as expected and it does not seem to look at that many rows. Here's tthe query chewed up and nicely displayed: SELECT ti.posi, ti.docid, d.filename, ti.id, c.name FROM corpus_documents cd, corpus c, documents d, tokens_ins ti, tokens t WHERE c.corpusid=4 AND cd.corpusid=c.corpusid AND cd.docid=d.docid AND t.docid=d.docid AND ti.id=t.id AND t.word='police' AND t.docid=ti.docid; ... 3791 rows in set (1 min 29.78 sec) Here are descriptions of the tables at play : mysql> desc tokens_ins; +------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------------+------+-----+---------+-------+ | id | int(11) | | MUL | 0 | | | posi | int(11) | | PRI | 0 | | | docid | mediumint(20) | | PRI | 0 | | | originalspelling | varchar(30) | | | | | +------------------+---------------+------+-----+---------+-------+ 4 rows in set (0.02 sec) mysql> desc tokens; +---------+--------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------------+------+-----+---------+-------+ | id | int(11) | | MUL | 0 | | | docid | mediumint(20) | | PRI | 0 | | | word | varchar(30) binary | | PRI | | | | pos | varchar(10) | | PRI | 0 | | | absfreq | mediumint(20) | | MUL | 0 | | +---------+--------------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) mysql> desc corpus; +----------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+----------------+ | corpusid | mediumint(20) | | PRI | NULL | auto_increment | | name | varchar(30) | | PRI |+-------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------------+------+-----+---------+-------+ | docid | mediumint(20) | | PRI | 0 | | | filename | varchar(30) | | PRI | | | | language | char(3) | | | | | | description | varchar(255) binary | YES | | NULL | | +-------------+---------------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> desc corpus_documents; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | corpusid | mediumint(20) | | PRI | 0 | | | docid | mediumint(20) | | PRI | 0 | | +----------+---------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) Can anybody give me a hand speeding up this ting? I'm running out of ideas. Thanks, P | | | language | char(3) | | MUL | | | +----------+---------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> desc documents; __________________________________ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]