CODEWITHSUNDEEP
mysqlperformancefull-text-search
sam
sam

Reputation: 317

Mysql performance with a large table

i have a table of resumes - names, resume text, zipcode, firstname, lastname, longitude, latitude, zip ... 500,000+ rows

i query it too ways:

by location, example:

1) SELECT * FROM resumes 
WHERE ((latitude BETWEEN 44.3523845787 AND 45.6809474213) 
AND (longitude BETWEEN -110.873064254 AND -108.993603746)) 
GROUP BY lastname,longitude 
LIMIT 0, 50 
by name

2) SELECT * from resumes 
   (MATCH(resume) AGAINST ('donald')) AS relevance 
    FROM resumes 
    WHERE (MATCH(resume) AGAINST ('donald')) 
    GROUP BY lastname,longitude 
    ORDER BY relevance DESC 
    LIMIT 0, 50

queries on this table are very slow at first, but same query afterwards is faster, i think it's caching it ...

how can i speed up these queries? thanks

Upvotes: 2

Views: 653

Answers (2)

Johan
Johan

Reputation: 76537

  1. Use indexes on all fields used to join tables.
  2. Use indexes on the fields that you use in your where clause.
  3. Don't use 'select *' select only the fields that you need.
  4. Group by sorts your resultset on the grouped fields, if you an order by on a different field (or a different order) you are forcing an extra sort slowing things down.

  5. MySQL does shortcut evaluation put the condition that limits the maximum number of rows first in your where clause.

  6. Select * with a group by is a different way of writing "eliminate duplicate rows" if you lay out your tables so there are no duplicate rows to begin with you don't need the group by either. This will speed up your queries a lot.

  7. Specify your lat and longitude field as type point and put a spatial index on those. I'd give you a link but I'm on the iphone now so that's a bit of a hassle now.

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78433

1) SELECT * FROM resumes 
WHERE ((latitude BETWEEN 44.3523845787 AND 45.6809474213) 
AND (longitude BETWEEN -110.873064254 AND -108.993603746)) 
GROUP BY lastname,longitude 
LIMIT 0, 50 
by name

This one can make little use of btree indexes. At best, it would grab all locations that fit for the latitude, or the longitude, and investigate potential rows along the other dimension. What you want is for it to only investigate rows that fit in a smaller box.

For this, you need a spacial index.

2) SELECT * from resumes 
   (MATCH(resume) AGAINST ('donald')) AS relevance 
    FROM resumes 
    WHERE (MATCH(resume) AGAINST ('donald')) 
    GROUP BY lastname,longitude 
    ORDER BY relevance DESC 
    LIMIT 0, 50

Likewise, this also needs a special kind of index which is not btree -- a full text index to be specific.

Upvotes: 1

Related Questions