I need to speed up this query in mysql

SELECT s.id, p.distance
FROM tbn_stockist_list s, tbn_postal p, tbn_stockist_to_cat c
WHERE
p.distance < 100 AND
((p.from_post = '3191' AND p.to_post = s.postal)
OR (p.to_post = '3191' AND p.from_post = s.postal))
AND s.state != 'NZ'
AND c.stockist = s.id
AND c.category = 1
ORDER BY p.distance LIMIT 10

tbn_stockist_list = 1200 records
tbn_postal = 385 000 records
tbn_stockist_to_cat = 2000 records

The search often takes over a minute on a php webpage. I know it could be improved, but don't have the knowledge to figure out how. I am sure the fact that I only ever need the first 10 results is key to speeding up the query.

Thanks!

 

Try a union CLAUSE

(SELECT 
	s.id, 
	p.distance as pdistance
FROM 
	tbn_stockist_list s, 
	tbn_postal p, 
	tbn_stockist_to_cat c
WHERE
	p.distance < 100 AND
	p.from_post = '3191' AND 
	p.to_post = s.postal
	AND s.state != 'NZ'
	AND c.stockist = s.id
	AND c.category = 1)
UNION
(SELECT 
	s.id, 
	p.distance as pdistance
FROM 
	tbn_stockist_list s, 
	tbn_postal p, 
	tbn_stockist_to_cat c
WHERE
	p.distance < 100 AND
	p.from_post = '3191' AND 
	p.from_post = s.postal
	AND s.state != 'NZ'
	AND c.stockist = s.id
	AND c.category = 1)
ORDER BY pdistance LIMIT 10

Code (markup):

Also check that you have index on all fields
which are in the where clause.

 

You can find out which indexes are being used by running an explain plan for your query:

EXPLAIN SELECT s.id, p.distance
FROM tbn_stockist_list s, tbn_postal p, tbn_stockist_to_cat c
WHERE
p.distance < 100 AND
((p.from_post = '3191' AND p.to_post = s.postal)
OR (p.to_post = '3191' AND p.from_post = s.postal))
AND s.state != 'NZ'
AND c.stockist = s.id
AND c.category = 1
ORDER BY p.distance LIMIT 10

 

Thanks to both of you, both those tips helped me speed the query up quite a bit.

 

To speed up query, enable the MySQL query cache, before that you need to set few variables in mysql configuration file (usually is my.cnf or my.ini)

- 1st, set query_cache_type to 1. (There are 3 possible settings: 0 (disable / off), 1 (enable / on) and 2 (on demand).

query-cache-type = 1

- 2nd, set query_cache_size to your expected size. I’d prefer to set it at 20MB.

query-cache-size = 20M