Re: Slow searches in large database

Start by checking the MySQL docs to find out if indexes are used with "IN" and "LIKE" especially as the latter is using wildcards. I suspect not. Given that you are essentially performing a sequential read of the database and checking on all these fields your performance is remarkably good. Soltuion? Use indexes wherever possible and refine you query. Do you have to search on all those fields, is there any way of deriving a subset? Alternately, investigate MySQL full-text search and see if you can put that to work for you. I also recommend posting this question on the MySQL list as it's really a db, not a PHP question. Cheers - Miles Thompson At 11:54 PM 10/13/2003 +0100, Adrian Teasdale wrote:

Hi there Wondering if someone could help or give some advice. We have a mysql database that has approximately 20,000 records and has a total size of 125mb There are approximately 25 fields that we need to search each time that someone performs a search. We have installed TurckMMCache onto the server which speeded up the searching, but it still takes around 15 seconds for the results to be displayed. 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%') Basically, is there anything that anyone can immediately suggest that we need to do to speed things up? Thanks Ade -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php