5

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?

  • 1
    What indexes exist on the table? – 200_success Nov 4 '14 at 0:18
2

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.

| improve this answer | |
  • Since I have no index at the moment, I will try what you suggest to see how it works. There is no 'id' column, just the 3 listed. – vashavoc Nov 4 '14 at 1:52
  • And thank you for your how to add an index query line. Makes a huge difference. 10s down to 0.2s for my query. I don't fully grasp what its doing but I will research this more. Thanks again – vashavoc Nov 4 '14 at 15:51
  • Thank you for accepting. Please do research on covering indexes. Here is one of my posts on the advantage of using a covering index : dba.stackexchange.com/questions/31258/… – RolandoMySQLDBA Nov 4 '14 at 15:56
8

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.

| improve this answer | |
  • 2
    Nice and sneaky ;) – ypercubeᵀᴹ Nov 3 '14 at 21:13
  • 1
    You know something ? That is sneaky. +1 !!! – RolandoMySQLDBA Nov 3 '14 at 21:29
  • Hmm interesting, I will try this as well. – vashavoc Nov 4 '14 at 1:55
  • Tried to use this and it returns back more than the 800 I need (about 900-1000), probably due to my ping's timing. I ping about 1 per min-ish. My script tears through the 250 IP addresses in and then I force a 30s wait before it cycles back through. If no clients are offline and respond fast, it can lead to more than 1 ping per min. I will play with this and see what I can come up with - I can definitely see the advantage here. – vashavoc Nov 4 '14 at 15:49
0

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.

| improve this answer | |
  • 1
    How can this query be converted to a join? – ypercubeᵀᴹ Nov 3 '14 at 20:44
  • In case, I'm skeptical that the subquery is having any significant effect on performance. – Sam Dufel Nov 4 '14 at 0:07
  • 1.) no going to try to add this. 2.) it has no effect on the query speed at it stands currently. – vashavoc Nov 4 '14 at 1:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.