I'm about to try a full text index in a very similar situation, which has the potential to grow fairly big. I'd also be interested in hearing how MySQL's full text index works for your large dataset.
Thanks, --jeff ----- Original Message ----- From: "Luke Muszkiewicz" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, February 24, 2002 6:30 PM Subject: RE: help with big table search > Jaime: > > Have you tried creating a FULLTEXT index? If so, I'd like to hear about > how well it works on such a large database. > > http://www.mysql.com/doc/F/u/Fulltext_Search.html > > Best of luck. > > -luke > > Luke Muszkiewicz > Pure Development, LLC > http://puredev.com > > > Hi, > > > > I have a table and currently has about 1.6 million entries. > > It is a table of events with date/time and description of the event. > > > > mysql> describe eventlog; > > +-------------+------------------+------+-----+---------+----- > > -----------+ > > | Field | Type | Null | Key | Default | > > Extra | > > +-------------+------------------+------+-----+---------+----- > > -----------+ > > | id | int(10) unsigned | | PRI | NULL | > > auto_increment | > > | timestamp | int(10) unsigned | | MUL | 0 | > > | > > | description | char(100) | | | | > > | > > +-------------+------------------+------+-----+---------+----- > > -----------+ > > > > My task is to search this table for any particular word that may occur > > anywhere in the description field: > > ie. > > > > SELECT * FROM eventlog > > WHERE description LIKE '%pattern%' > > ORDER BY id DESC LIMIT 20; > > > > Considering that I am using LIKE instead of = as a search > > option, making > > "description" into an index would not do any good. > > > > This search works well ONLY if the items to search are relatively near > > the top of the table *AND* most importantly, there are at least '20' > > matching items available on the table. *IF* the table contains only > > 19 matches or less, then the SELECT will search through the whole > > table and I may have to wait some 2~3 minutes to get the result. > > > > > > Then I started using cross-reference table. > > > > mysql> describe crossref; > > +-------+------------------+------+-----+---------+-------+ > > | Field | Type | Null | Key | Default | Extra | > > +-------+------------------+------+-----+---------+-------+ > > | word | char(15) | | MUL | | | > > | id | int(10) unsigned | | MUL | 0 | | > > +-------+------------------+------+-----+---------+-------+ > > word is an independent index, > > id is an independent index > > > > For every entry into the eventlog table, I broken down each word from > > description and inserted them into the crossref table. This way, > > whenever I want to find the word "HELLO", all i need to do > > is: > > SELECT id FROM crossref WHERE word = 'HELLO' ORDER BY id DESC > > limit 20; > > and then use the results into another search: > > SELECT * from eventlog where id in (previous result); > > > > However still, for whatever reason, this search isnt working well. > > Though the search time is better than before, it still takes about > > 30~60 seconds for an answer. (sometimes fairly fast <5 seconds). > > SOMETIMES, searching through this crossref were actually slower. > > > > I'd like to know how you people come up with a very good table and > > search. Eventlog is currently 200MB in size. > > > > Jaime > > > --------------------------------------------------------------------- > 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
