lindellfoth

Thanks for the feedback. Doesn't sound like the temporary table idea is going to help much. Think I'm going to have to ditch the regular expression altogether.

Hi Would really appreciate some advice before I tackle this. I have a genealogical website with a large amount of information in a MySQL database taken from old publications that have been scanned. This means the data is in one text field and things like first name and surname are not seperated into seperate fields. Many are directory pages with many names on the page, and several instances of the same surname. Most searches are done on just the surname but I also have a surname and first name search which uses a regular expression to find first names that are close (within 6 characters) to a particular surname. The expression I've used in my SELECT query is as follows: $sql4 = " WHERE (page_text REGEXP '[[:<:]]".$surname."[[:>:]].{0,6}[[:<:]]".$firstname."[[:>:]]' OR page_text REGEXP '[[:<:]]".$firstname."[[:>:]].{0,6}[[:<:]]".$surname."[[:>:]]') The query uses the limit parameter to grab 20 results at a time for a set of multipage results. However, as the database has grown (currently around 60,000 rows, 415MB) this has got too slow. I was wondering if using a temporary table was the way to go to speed up my query - I would create a temporary table using just the surname then search that using the regular expression. If I ordered the temporary table results by the primary key (data_id) and then for the next page of results I could create a new temporary table starting from the next data_id number. Would this speed things up significantly and place less demand on the web server? My concern is that for a common surname such as Smith, I'm still creating a large table to query to start with, but I don't know how many rows will contain the first name - surname combination. As the database grows this could become a problem. Any advice on whether this is a sensible way to speed things up or suggestions for alternative methods would be very helpful.

That's brilliant, just what I need. Thanks

Thanks for the suggestion, but won't that match any uppercase or title case word? I need to match a specific word, input by the user. So if the user inputs the name "Law" for example, I need to find all instances of that particular word as title case ie "Law" or uppercase ie "LAW", but not instances that are all lowercase ie "law".

Hi I need to find all instances of a word in some text that is either in title case or uppercase but ignoring all lowercase instances. The function is used to locate surnames within the text. The problem is that some surnames eg "Law" are the same as common ordinary words so I need to find "Law" and "LAW" but not "law", so I can reduce the number of irrelevant results. My current code uses preg_match_all in case insensitive mode, but if I simply make it case sensitive then the user will only find "Law" or "LAW", depending on which version they entered. I think there is a function or regular expression to change the first letter of a word to uppercase, but I don't want to change it, just locate instances already existing in the text. Currently the code is: $pattern='/.{0,'.$prechars.'}\b'.$surname.'\b.{0,'.$endchars.'}/is'; preg_match_all($pattern, $page_text, $match); Anyone out there got any ideas? All help much appreciated.