MySQL – Speed of Query for Most Recent 800 Entries in Chronological Order

MySQLperformancequery-performance

I have a MySQL database running on an Ubuntu box that pings 250 clients once a minute (24×7). 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?

Best Answer

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.

Related Solutions

MySQL Query – How to get most recent demographics

Assuming you have a PRIMARY KEY or otherwise UNIQUE KEY called id (replace with whatever it's called in your table), consider the following query:

SELECT
  cardnbr,
  fname,
  lname,
  address,
  city,
  state,
  postal
FROM
  cards,
  (
    SELECT 
      GROUP_CONCAT(top_id_per_group) AS top_ids
    FROM
      (
        SELECT 
          SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY lastvisit DESC), ',', 1) AS top_id_per_group
        FROM
          cards
        GROUP BY
          cardnbr
      ) s_top_ids_per_group
  ) s_top_codes
WHERE
  FIND_IN_SET(id, top_ids)
ORDER BY
  cardnbr
;

The idea is to get, per cardnbr, the id where the lastvisit is most recent. Then, get all row data for those ids.

The above is a simplification of Selecting Top N Records Per Group, where N = 1.

Another way to solve it is described in Selecting Non Aggregated Column Data in GROUP BY. The query presented in this post actually provides better performance than the above, but uses some parsing and casting to get the final results (all columns parsed as strings, then cast back to their original types). Also see this post. Apologies for the many links, but each post presents with a different solution to the problem.

MySQL: Keep the 10 most recent rows for each user

I found figured out an answer that works pretty well, though if you have a lot of users it may take some time to execute. I have yet to do any time testing with it. It also assumes you have an id field that uniquely identifies each score.

The way it works is in the inner query it finds up to the 10 most recent scores. Then deletes everything not returned by the inner query.

DELETE FROM score
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT *
    FROM score s1
    WHERE (
        SELECT COUNT(*)
        FROM score s2
        WHERE s1.userid = s2.userid
            AND s1.timestamp <= s2.timestamp
    ) <= 10 --Keep this many records
  ) foo
);

Best Answer

UPDATE 2014-11-03 16:05 EST

Related Solutions

MySQL Query – How to get most recent demographics

MySQL: Keep the 10 most recent rows for each user

Related Question