What version of MySQL are you using? Have you checked the cardinality on
these tables?
-----Original Message-----
From: Patrick Drouin
To: [EMAIL PROTECTED]
Sent: 7/15/04 8:27 AM
Subject: Need help optimizing query
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
