Use a Temporary Table?

[lindellfoth]

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.

[fenway]

Well, the temporary table is still on disk.

 

Granted, you'll save the random disk I/O on the larger table.

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

[fenway]

Well, there are other full-text searching options.  And MEMORY tables aren't terrible, provided there aren't many.