You may also want to google for mysql optmization hacks to help tune the mysql daemon itself.
Jason
Cesar Cordovez wrote:
Hi!
I think that you will need a "keyword" table to speed up this procedure. The basic idea is to create an index of words in your fields, therefore you will not use "like" but "=" making things run much, much faster.
The steps for doing this are:
1. Every time you save a record in the table docs, take all the text fields in it and separate it into words. You can use explode to do this. Like for example: $words = explode(" ", $record["fieldone"] . " " .$record["fieldtwo"], {etc, etc});
2. save in the keyword table the non repeating words in the array with a reference to the original document, for example the document id.
3. Then, if you want to search for, let say, people you will do:
select distinct(docid) from keywords where word='people'
and you will have a list (cursor) with all the documents that have the word "people".
Very fast.
For an example of this, go to http://www.alenet.com and search for something. With very little more effort you can add a spelling thingy (type peeple on the search field) and "porcentajes" for each document.
I use the exactly the same procedure in a 5000 record database, and the time it uses to search is about the same that in alenet.com
Cesar
An example of one of our search strings is:
select docs.* from docs where 1 and CY IN ('GB') and (TI like
'%searchstring%' or PD like '%searchstring%' or ND like '%searchstring%'
or DR like '%searchstring%' or DS like '%searchstring%' or DD like
'%searchstring%' or DT like '%searchstring%' or RN like '%searchstring%'
or HD like '%searchstring%' or TD like '%searchstring%' or NC like
'%searchstring%' or PR like '%searchstring%' or RP like '%searchstring%'
or AA like '%searchstring%' or TY like '%searchstring%' or AC like
'%searchstring%' or PC like '%searchstring%' or RC like '%searchstring%'
or RG like '%searchstring%' or AU like '%searchstring%' or TW like
'%searchstring%' or CO like '%searchstring%' or AB like '%searchstring%'
or TX like '%searchstring%')
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
