Optomizing MySQL - WHERE and ORDER BY use different columns

Question

I have a 115MByte, 1,600,000 row table that stores house prices and coordinates. 'SELECT' queries are taking too long. I was wondering if anyone can suggest a way of speeding it up.

Slightly simplified version of my table....

CREATE TABLE `prices1` (
    `price` INT(10) NOT NULL,
    `address` VARCHAR(127) NOT NULL,
    `lat` FLOAT(6,4) NOT NULL COMMENT 'GPS latitude',
    `lng` FLOAT(6,4) NOT NULL COMMENT 'GPS longitude',
    INDEX `lat` (`lat`),
    INDEX `price` (`price`)
) ENGINE=MyISAM ROW_FORMAT=DEFAULT

This query extracts records within a 2 mile x 2 mile square area ....

SELECT * FROM prices1
WHERE lat >= 55.9430 AND lat <= 55.9641
AND lng >= -3.2279 AND lng <= -3.1901                 
AND price >= 100000 and price <= 400000
ORDER BY price asc
LIMIT 50

... it takes 0.8 seconds. EXPLAIN says it is using index 'price'.

If I use force index(lat) the query runs in 0.1 seconds - much faster but I'd really like it faster still.

The query will be run via a web-site with multiple users. All queries will be similar but with different ranges for lat, lng and price. Table write speed is not important.

Can anyone suggest how I can speed up the query? Or at the least encourage MySQL to use index 'lat' when its a better choice (I'd rather not use 'force lat' as I found queries extracting from a larger area are better run using the 'price' index).

I've run 'ANALYZE' and 'OPTOMIZE'.

I've read up on MySQL 'order by indexation' - where the same index can be used for the 'WHERE' and 'ORDER BY' clauses but I dont think this can be used where the WHERE is a range?

Is it worth pursuing 'spacial indexes'?

Any ideas welcome as I really need to get this much faster.

Answer 1

What happens if you do:

INSERT INTO temporal
SELECT * FROM prices1
WHERE lat >= 55.9430 AND lat <= 55.9641
AND lng >= -3.2279 AND lng <= -3.1901                 
AND price >= 100000 and price <= 400000



SELECT * from temporal
ORDER BY price asc
LIMIT 50