[nycphp-talk] Ideas on how to speed up search process
smanes at magpie.com
Mon Aug 20 23:35:52 EDT 2007
Nelly Yusupova wrote:
> 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 slow...an average query takes about 10 seconds to
> display in a browser, which is way too long.
> I tried optimizing the queries and indexed the table, which is very hard
Without seeing your data model or even knowing what database you're
using that question is almost impossible to answer. Bear in mind
however that indexed columns aren't a magic bullet, especially if you
have lots of repeating data in them.
Use your query plan analyzer to figure out where the sluggishness is,
paying close attention to "full table scan" or scans where the index
file is being tossed. If your database supports partitioning you can
get quite a bit of improvement there. Adding a second mirrored database
just for job searches is another idea. Minimize text searches;
normalize common elements like start date, industry, category, job
I've written four job boards and each employed different techniques
specific to the business requirements. One thing you might try however
is to dump the jobs as text and let local search engine software index
them several times a day. There are a lot of API tricks you can use to
specialize those searches.
More information about the talk