Have you considered splitting this into two queries? One query can gather information from your token and token_ins tables. The other would join the first queries results to the other tables to complete your original query. Depending on your data, one of these subqueries should return a smaller set (fewer rows) and you should execute the smaller one FIRST. Because of that you may want to flip the order of execution of these queries around (modifying as appropriate for the new order) and check the cd, c, and d tables first and join those results to the t, ti, t2 and ti2 tables..... You will only know by testing.
CREATE TEMPORARY TABLE tmpTokens SELECT ti.posi, ti.docid,ti.id FROM tokens_ins ti, tokens t, tokens t2, tokens_ins ti2 WHERE ti.id=t.id AND ti2.id=t2.id AND t.docid=ti.docid AND t2.docid=ti2.docid AND t.docid=t2.docid AND t.word='faire' AND t2.word='présence' AND ti2.posi>=ti.posi-5 AND ti2.posi<=ti.posi+5; SELECT t.posi, t.docid, d.filename, t.id, c.name FROM corpus_documents cd, corpus c, documents d, tmpTokens t WHERE c.corpusid=4 AND cd.corpusid=c.corpusid AND cd.docid=d.docid AND t.docid=d.docid DROP TABLE tmpTokens Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Patrick Drouin <[EMAIL PROTECTED]> wrote on 07/15/2004 11:36:18 AM: > Hum, > > Well, I'm back with another one... When adding a > join to the previous query, it sloows down once again > even though it retrieves less datat. Here's the info : > > > 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, tokens t2, tokens_ins ti2 > 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 ti2.id=t2.id > AND t.docid=ti.docid > AND t2.docid=ti2.docid > AND t.docid=t2.docid > AND t.word='faire' > AND t2.word='présence' > AND ti2.posi>=ti.posi-5 > AND ti2.posi<=ti.posi+5; > +-------+------ > +-------------------------------------------------------------- > +-------------------+---------+---------------+------ > +--------------------------+ > | table | type | possible_keys > | key | key_len | > ref | rows | Extra | > +-------+------ > +-------------------------------------------------------------- > +-------------------+---------+---------------+------ > +--------------------------+ > | c | ref | PRIMARY,corpus_corpusid > | PRIMARY | 3 | > const | 1 | Using where; Using index | > | cd | ref | PRIMARY > | PRIMARY | 3 | > const | 2 | Using where; Using index | > | d | ref | PRIMARY,documents_docid > | PRIMARY | 3 | > cd.docid | 3 | Using index | > | t | ref | > PRIMARY,tokens_id,tokens_docid,tokens_word,tokens_word_docid > | tokens_word_docid | 33 | const,d.docid | 1 | > Using where | > | t2 | ref | > PRIMARY,tokens_id,tokens_docid,tokens_word,tokens_word_docid > | tokens_word_docid | 33 | const,t.docid | 1 | > Using where | > | ti | ref | > PRIMARY,tokens_ins_id,tokens_ins_docid > | tokens_ins_id | 4 | t.id | > 96 | Using where | > | ti2 | ref | > PRIMARY,tokens_ins_id,tokens_ins_posi,tokens_ins_docid > | tokens_ins_id | 4 | t2.id | > 96 | Using where | > +-------+------ > +-------------------------------------------------------------- > +-------------------+---------+---------------+------ > +--------------------------+ > 7 rows in set (0.00 sec) > > Seems OK to me. But here's the query once run: > > mysql> SELECT ti.posi, ti.docid, d.filename, ti.id, > c.name FROM corpus_documents cd, corpus c, documents > d, tokens_ins ti, tokens t, tokens t2, tokens_ins ti2 > 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 ti2.id=t2.id AND t.docid=ti.docid AND > t2.docid=ti2.docid AND t.docid=t2.docid AND > t.word='faire' AND t2.word='présence' AND > ti2.posi>=ti.posi-5 AND ti2.posi<=ti.posi+5; > +----------+-------+-----------------+------+---------------+ > | posi | docid | filename | id | name > | > +----------+-------+-----------------+------+---------------+ > | 2798734 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 4036372 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 4324477 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 5167534 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 5535744 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 6796883 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 7484157 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 9410455 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 10251037 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 11391576 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 11656957 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 12069808 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 15313652 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 15663061 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 18024245 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 18341956 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 20943027 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > | 21282138 | 366 | LeMonde2002.tag | 1110 | Le Monde > 2002 | > +----------+-------+-----------------+------+---------------+ > 18 rows in set (2 min 53.60 sec) > > Victor, you were asking about cardinality, here's the > output of the indexes. I'm not sure I understand what > cardinality is about and how it would interfere: > > mysql> show index from tokens; > +--------+------------+-------------------+-------------- > +-------------+-----------+-------------+----------+--------+------ > +------------+---------+ > | Table | Non_unique | Key_name | > Seq_in_index | Column_name | Collation | Cardinality | > Sub_part | Packed | Null | Index_type | Comment | > +--------+------------+-------------------+-------------- > +-------------+-----------+-------------+----------+--------+------ > +------------+---------+ > | tokens | 0 | PRIMARY | > 1 | word | A | 249541 | NULL | > NULL | | BTREE | | > | tokens | 0 | PRIMARY | > 2 | pos | A | 374312 | NULL | > NULL | | BTREE | | > | tokens | 0 | PRIMARY | > 3 | docid | A | 748624 | NULL | > NULL | | BTREE | | > | tokens | 1 | tokens_id | > 1 | id | A | 249541 | NULL | > NULL | | BTREE | | > | tokens | 1 | tokens_docid | > 1 | docid | A | 246 | NULL | > NULL | | BTREE | | > | tokens | 1 | tokens_word | > 1 | word | A | 249541 | NULL | > NULL | | BTREE | | > | tokens | 1 | tokens_asbfreq | > 1 | absfreq | A | 2478 | NULL | > NULL | | BTREE | | > | tokens | 1 | tokens_word_docid | > 1 | word | A | 249541 | NULL | > NULL | | BTREE | | > | tokens | 1 | tokens_word_docid | > 2 | docid | A | 748624 | NULL | > NULL | | BTREE | | > +--------+------------+-------------------+-------------- > +-------------+-----------+-------------+----------+--------+------ > +------------+---------+ > 9 rows in set (0.00 sec) > > mysql> show index from tokens_ins; > +------------+------------+------------------+-------------- > +-------------+-----------+-------------+----------+--------+------ > +------------+---------+ > | Table | Non_unique | Key_name | > Seq_in_index | Column_name | Collation | Cardinality | > Sub_part | Packed | Null | Index_type | Comment | > +------------+------------+------------------+-------------- > +-------------+-----------+-------------+----------+--------+------ > +------------+---------+ > | tokens_ins | 0 | PRIMARY | > 1 | docid | A | 246 | > NULL | NULL | | BTREE | | > | tokens_ins | 0 | PRIMARY | > 2 | posi | A | 24935821 | > NULL | NULL | | BTREE | | > | tokens_ins | 1 | tokens_ins_id | > 1 | id | A | 259748 | > NULL | NULL | | BTREE | | > | tokens_ins | 1 | tokens_ins_posi | > 1 | posi | A | 24935821 | > NULL | NULL | | BTREE | | > | tokens_ins | 1 | tokens_ins_docid | > 1 | docid | A | 246 | > NULL | NULL | | BTREE | | > +------------+------------+------------------+-------------- > +-------------+-----------+-------------+----------+--------+------ > +------------+---------+ > 5 rows in set (0.00 sec) > > Any help would be more than appreciated. > > > > __________________________________ > Do you Yahoo!? > Read only the mail you want - Yahoo! Mail SpamGuard. > 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] >
