Re: Slow searches in large database

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%')