Yes of course. Here you go:
mysql> EXPLAIN SELECT DISTINCT w0.l_id FROM law_words as w0
-> inner join law_words as w1 on w0.l_id=w1.l_id
-> WHERE
-> w0.w_id IN (258,282,287, 615, 1101, 1949, 1968, 3417, 3574,
3578, 3643,
-> 4345,4768, 5297, 5976,6133, 7243,7245, 9271, 9348, 11146,
11150, 11172,
-> 11232,11847, 12542, 12859, 14811, 24839, 26653,27662) AND
-> w1.w_id IN (405, 2017,2192, 2592, 2595, 2603, 2981, 4055, 4068,
4346,5755, 6480, 9384,
-> 9408, 11513, 11514, 12126, 12134, 12638, 13052, 13643, 13769,
13836, 13945, 14154, 14693, 14867,
-> 14980, 15518, 15557, 17830, 19005, 19051, 19247, 20176, 20926,
22364, 22365, 22366, 22732, 24668,
-> 24793, 24956, 25286, 26242, 26665, 26847, 27144, 27348, 27815,
28494, 30910, 31878, 32161,
-> 33586, 34396);
+-------+-------+---------------+---------+---------+------+------+---
---------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+-------+---------------+---------+---------+------+------+---
---------------------------------------+
| w0 | range | PRIMARY | PRIMARY | 4 | NULL | 473 |
where used; Using index; Using temporary |
| w1 | range | PRIMARY | PRIMARY | 4 | NULL | 479 |
where used; Using index; Distinct |
+-------+-------+---------------+---------+---------+------+------+---
---------------------------------------+
2 rows in set (0.01 sec)
----- Original Message -----
From: "Victor Pendleton" <[EMAIL PROTECTED]>
To: "'Artem Koutchine'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, February 11, 2003 5:29 PM
Subject: RE: Very slow request with many ORs in where parts
> Can you post your explain plan?
>
> -----Original Message-----
> From: Artem Koutchine [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, February 11, 2003 8:23 AM
> To: [EMAIL PROTECTED]
> Subject: Very slow request with many ORs in where parts
>
>
> Hi!
>
> I have the following table:
>
> create table law_words (
> l_id int unsigned not null,
> w_id int unsigned not null,
> primary key (w_id, l_id)
> );
>
>
> The request is:
>
> SELECT DISTINCT w0.l_id FROM law_words as w0
> inner join law_words as w1 on w0.l_id=w1.l_id
> where w0.w_id in (258,282,... about 30 ids) and
> w1.w_id in (405, 2017, and so on about 50 ids);
>
> The basic idea is thart law_words hold index of
> words (w_id) for each law (l_id), so law can be found by words,
which
> are specified by user and the their ids are looked up in
> vocabular.
>
> Now law_words has 288000 records and that request takes
> about 1 second on a pc with 1GB of RAM and dual Pentium III XEON
> 550Mhz,
> which is TOO MUCH!
> Explain show thart mysql is
> using ' range' and primary index, and about 400 records for each
> table.
>
> For three specified words request takes about forever, so no search
is
> possible for three words. The request is using INNER JOIN to get
> the words in the 'AND' manner (laws which contain ALL specified
> words).
>
> I don't understand what i am doing wrong, since i thought it is
> a basic technology behind any search engine.
>
> Please, help, if you can.
>
> Regards, Artem
>
>
> --------------------------------------------------------------------
-
> 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
>
> --------------------------------------------------------------------
-
> 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
>
---------------------------------------------------------------------
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
