Speed of MySQL query for the most recent 800 entries in chronological order

Question

I have a MySQL database running on an Ubuntu box that pings 250 clients once a minute (24x7). My column names are:

ip_address varchar(16),
status varchar(7),
timestamp datetime 

Been running it for several weeks and it has grown (10,581,421+ rows). I run a query to return the most recent 800 results on a specific IP address:

SELECT *
FROM (
    SELECT Device_ip, Status, timestamp
    FROM ping_results
    where Device_ip = '192.168.1.1'
    order by timestamp desc
    LIMIT 800
) SUB ORDER BY timestamp asc;

It takes 10+ seconds to return the results. Is there anything I can do differently to speed this up?

Answer 1

Looking at the query, I see you need to retrieve the 800 most recent pings in ascending order.

You should be able to improve the query with the following index

ALTER TABLE ping_results ADD INDEX DEV_TIME_IP_NDX (`Device_ip`,`timestamp`,`ip_address`);

This will help your query in the following manner

• The ORDER BY is quickly reduced to a backward index scan on a specific device_ip
• Since all three columns are in the index, the table is not used to retrieve any data

Give it a Try !!!

UPDATE 2014-11-03 16:05 EST

If the ping_results table has an id column, you could probably redo the query using JOIN

SELECT B.Device_ip, B.Status, B.timestamp FROM
(
    SELECT id FROM
    (
        SELECT id,timestamp FROM ping_results
        where Device_ip = '192.168.1.1'
        order by timestamp desc LIMIT 800
    ) SUB ORDER BY timestamp
) A LEFT JOIN ping_results B USING (id);

After making the index I suggested, you should run the explain plan against this query and your original query. Then, select the best explain plan or the fastest running query. Chances are, you first query should be adequate because it has "less noise" to deal with in the Query Optimizer.

BTW I use LEFT JOIN because the id value will stay in the order is was made from the subquery. Doing INNER JOIN will do an inadvertent reorder of the keys.

Answer 2

You could break out of that subquery like this:

SELECT Device_ip, Status, timestamp
    FROM ping_results
    WHERE Device_ip = '192.168.1.1'
    AND timestamp > DATE_SUB(NOW(), INTERVAL 800 MINUTE )
    ORDER BY timestamp ASC

That should simplify the query plan a bit, and you're only doing one ORDER BY instead of two.

As everyone else also mentioned, indexes are a good idea for improving performance, and will probably do more than manipulating the query.

Answer 3

Two things:

1) do you have indexes? What does show index from ping_results say?

2) Don't use subqueries. Convert the query to a join statement.