On Wed, 12 Feb 2003 15:50:29 +0300 Artem Koutchine <[EMAIL PROTECTED]> wrote:
> Hi! > > I have posted this message yerstaday, but apperantly > did not give enough information for reader to figure out > what's going on. So, i am reposting it with more information. > > 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,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); > > The basic idea is thart law_words holds 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 > vocabulary table. > > Now law_words has 228207 records and that request takes > about 2 seconds on a pc with 1GB of RAM and dual Pentium III XEON > 550Mhz, which is TOO MUCH! Explain shows thart mysql is > using ' range' and primary index, and about 500 records for each > table. Try add index on (l_id,w_id) and see if it helps. And see if it runs faster without distinct. -- _/ _/ _/_/_/ ----- Rafał Jank [EMAIL PROTECTED] ----- _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215625 _/ _/ _/ --------==* http://szukaj.wp.pl *==---------- --------------------------------------------------------------------- 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
