NYCPHP Meetup

[Steve Manes]

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 
position, etc.

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.