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
-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
