[nycphp-talk] Ideas on how to speed up search process

John Campbell jcampbell1 at
Mon Aug 20 23:45:34 EDT 2007

> I have a table that contains about 40,000 rows of job posting data.   There
> is a script that allows users to search for jobs using numerous search
> criteria.  Because of all the criteria, the search process is very
> average query takes about 10 seconds to display in a browser, which is way
> too long.

What database are you using?  Are there any wildcards in the query of
the form "LIKE '%$term"?  If so, you should try full text indexes on
those columns and use MATCH/AGAINST assuming you are using mysql.

>  One idea that I have is to create an XML document of all
> of the job posts and write a search script that searches the XML document.

XML is going to be a dead-end unless you have a tool to index the XML.
 Creating a search engine for 40k records should be doable with just
SQL/ full text indexes. You shouldn't need anything specialized until
you hit about 0.5M records, in which case you should probably use
specialized software (e.g. Lucene).

-John Campbell

More information about the talk mailing list