Split MySQL table into 2? Current search is too slow

Question

I am using a MySQL table to store data of things on a map which will be searched by the user. The site allows the user to add in additional filters to narrow his results.

Main MySQL Query:

SELECT listing_id, listings.property_1, property_2, address_1, address_2, city, listings.lat, listings.lng, img_subpath, photos, some_timestamp
    FROM table
    WHERE listings.lat BETWEEN 61.123 AND 61.124
    AND listings.lng BETWEEN 40.123 AND 40.124
    AND some_timestamp BETWEEN DATE_SUB( NOW(), INTERVAL 0 DAY) AND DATE_SUB( NOW(), INTERVAL 5 DAY)')
    JOIN (SELECT price, lat, lng, MAX(some_timestamp) as latest FROM listings GROUP BY price, lat, lng) as t2', 'listings.price=t2.price AND listings.lat=t2.lat AND listings.lng=t2.lng AND listings.some_timestamp=t2.latest', 'inner')
    LIMIT 200,0

Columns:

(Indexed columns are in bold)

(columns that start with property_ are optional filters except the first 2)

listing_id int(8)
url varchar(255)
city varchar(32)
somename varchar(32)
price int(7)
price_per int(7)
property_1 int(2)
property_2 int(2)
address_1 varchar(255)
address_2 carchar(255)
city varchar(64)
state varchar(32)
postal int(6)
some_timestamp timestamp
another_timestamp timestamp
lat float(10)
lng float(10)
description text
img_subpath varchar(15)
photos text
reply_email varchar(255)
phone varchar(16)
property_3 tinyint(1)
property_4 tinyint(1)
property_5 tinyint(1)
property_6 tinyint(1)
property_7 tinyint(1)
property_8 tinyint(1)
property_9 tinyint(1)
property_10 tinyint(1)
property_11 tinyint(1)
property_12 tinyint(1)
property_13 tinyint(1)
property_14 tinyint(1)

Problem: The database takes too long (3-4seconds) to fetch the results. How can it be sped up to <0.5sec which has been done on very similar sites?

Should I have 2 tables, the first table will contain only columns involved in the search, and the next table contains all the other columns. So when the search happens, it searches the first table, gets the listing_id of the results, then use a IN clause to retrieve all the other data from the second table?

Please advise, thank you!

Your query's got some syntax errors on the self join/subselect as you've pasted it above. — Marc B
I'm using Codeigniter's Active record syntax and tried translating to the full MySQL query. Not too sure how is the actual SQL code like — Nyxynyx

Mosty Mostacho Mosty Mostacho · Accepted Answer · 2012-02-09T03:21:47

I think you're trying to get the timestamps of the last five days with this:

AND some_timestamp BETWEEN DATE_SUB( NOW(), INTERVAL 0 DAY)
AND DATE_SUB( NOW(), INTERVAL 5 DAY)')

If that is the case, it should be the the same as

AND some_timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 5 DAY)) AND NOW()

And if you don't have records with some_timestamp with a future value you can use

AND some_timestamp > DATE_SUB(NOW(), INTERVAL 5 DAY))

Now, I THINK this should work faster

SELECT listing_id, listings.property_1, property_2, address_1, address_2, city,
    listings.lat, listings.lng, img_subpath, photos, some_timestamp
FROM t2
LEFT JOIN listings
    ON (listings.price=t2.price AND listings.lat=t2.lat AND listings.lng=t2.lng
        AND t2.some_timestamp < listings.some_timestamp)
WHERE listings.lat BETWEEN 61.123 AND 61.124
    AND listings.lng BETWEEN 40.123 AND 40.124
    AND some_timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 5 DAY)) AND NOW()
    AND t2.some_timestamp is null
LIMIT 200

PS: I don't think splitting the table would improve performance much... actually, if you have to join both tables to get a result, it'll take more

Split MySQL table into 2? Current search is too slow

2 Answers