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
