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.

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.

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
(
    SELECT id FROM (SELECT id,timestamp FROM ping_results where Device_ip = '192.168.1.1'
    order by timestamp desc LIMIT 800) SUB1) 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.

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.

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 !!!

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
(
    SELECT id FROM (SELECT id,timestamp FROM ping_results where Device_ip = '192.168.1.1'
    order by timestamp desc LIMIT 800) SUB1) 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.