Re: [PHP] Slow searches in large database

From: Rory McKinley (rorym_at_nebula.co.za)
Date: 10/14/03 

To: <adrian@justcompete.com>, <php-general@lists.php.net>
Date: Tue, 14 Oct 2003 08:54:25 +0200

Hi Adrian

Somewhere in the back of my mind..I remember reading that if you are using
LIKE putting in wildcards for and aft e.g %string% slows down queries
somewhat....
I will have a look at my mySQL stuff and see if I can confirm...but if I
recall correctly, is there any way you can do away with one of the
wildcards?

Rory McKinley
Nebula Solutions
+27 82 857 2391
rorym@nebula.co.za
"There are 10 kinds of people in this world,
those who understand binary and those who don't" (Unknown)
----- Original Message -----
From: "Adrian Teasdale" <adrian@justcompete.com>
To: <php-general@lists.php.net>
Sent: Tuesday, October 14, 2003 12:54 AM
Subject: [PHP] Slow searches in large database

> 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
>
>
>

Relevant Pages

  • Re: WildCard Key
    ... But I have a new need to support filenames based on wildcards. ... I'd use a prefix tree aka trie both ... An empty string matches a terminal vertex ... stars that contains at least one star, ...
    (microsoft.public.vc.stl)
  • Re: wildcard woes
    ... Thinking the wildcards would work I try this ... In brief, Filemaker does a word search from the beginning of a word, for any string you put into a field. ... So for your desired searching a single request with "red yellow" in the field will work. ... These might be of assistance if you look them up in the help file and see how they are used in FM. ...
    (comp.databases.filemaker)
  • Re: Help configuring Symantec NIS ad blocking - tech support is worthless
    ... > Wildcards do not work when adding strings to the NIS ad blocking list. ... finally issued a specific response. ... will match for the length of the supplied rule string. ...
    (comp.security.firewalls)
  • Re: Find and Replace
    ... wildcards but will just have to sit down and get to grips with them through ... Greg Maxey/Word MVP ... MarkN wrote: ... Again <s anchors the start of a find string to a word starting with ...
    (microsoft.public.word.docmanagement)
  • Re: Problems with TextFile parsing
    ... just moving the window should just queue up a few messages and that's ... Now it slows down if I do anything. ... procedure Split(S, Delimiter: string; Strings:TStrings); ... Strings.Add(Copy(S, OldP, Length(S))); ...
    (borland.public.delphi.language.objectpascal)