MYSQL query speed (or lack there Speed of) MySQL query for the most recent 800 entries in chronological order

Tweeted twitter.com/#!/StackDBAs/status/529414942336638977

occurred Nov 3, 2014 at 23:28

improved formatting

Source Link

edited Nov 3, 2014 at 20:33

Colin 't Hart

9.4k
15
36
44

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).

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

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

"SELECTSELECT *  
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"asc;

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

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 its 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?

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;