Artem, Have you considered using a full text index? I don't really understand exactly what you are trying to do, but consider it if you haven't already.
Andy > -----Original Message----- > From: Artem Koutchine [mailto:[EMAIL PROTECTED]] > Sent: 11 February 2003 14:23 > 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
