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

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?

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.

StackExchange Database Administrators Q#81769, answer score: 8