Roq Posted January 18, 2010 Share Posted January 18, 2010 Basically I have a database that looks something like this: id hostname pingmin pingavg pingmax pingdev pingloss lastupdated 145446 ts3.bo4 83.83 84.21 84.69 0.21 0.0 2010-01-18 13:48:15 145445 ts4.bo4 83.74 84.11 84.66 0.18 0.0 2010-01-18 13:48:08 145444 ts2.bo3 85.71 85.96 86.23 0.12 0.0 2010-01-18 13:48:01 145443 ts2.se1 78.89 79.13 80.66 0.37 5.0 2010-01-18 13:47:54 145442 ts1.bo4 83.82 84.12 84.68 0.19 0.0 2010-01-18 13:47:46 This database is generated by a pingscript that pings a list of devices (roughly 70 devices) 20 times each, every 10 minutes, then adds an entry into the database with the above information. I store multiple rows for each hostname so that I can use the data to draw graphs with. However for a basic status page I just want to find the latest entry for each device and display the data. What is the best way to do this? I can't seem to get MAX to work with a date field, or maybe I can't get the WHERE clause right, either way I'm stuck. Can someone please help me with a query? database name = switchbase tablename = corp_pingdb column names = headers from the above table, those are accurate Thanks! Quote Link to comment Share on other sites More sharing options...
Roq Posted January 19, 2010 Author Share Posted January 19, 2010 Just bumping this since I haven't received a response. Is there any more information you'd like me to provide? Thanks Quote Link to comment Share on other sites More sharing options...
fenway Posted January 20, 2010 Share Posted January 20, 2010 You need to combine MAX() with GROUP BY. Quote Link to comment Share on other sites More sharing options...
Roq Posted January 20, 2010 Author Share Posted January 20, 2010 I believe you mean something like this, which is what I've been trying but doesn't seem to work. The results it spits out are dated Jan 4th (when I started collecting data). SELECT MAX(`id`), `hostname`, `pingmin`, `pingavg`, `pingmax`, `pingdev`, `pingloss`, `lastupdated` FROM `switchbase`.`corp_pingdb` GROUP BY `hostname` Quote Link to comment Share on other sites More sharing options...
Roq Posted January 20, 2010 Author Share Posted January 20, 2010 Update: This query seems to work correctly, but takes an unacceptable amount of time to execute, maybe 5-10s for the page to load. Any suggestions for a way to optimize this, or perhaps a different way to do the same thing? SELECT `corp_pingdb`.`id`, `corp_pingdb`.`hostname`, `corp_pingdb`.`pingmin`, `corp_pingdb`.`pingavg`, `corp_pingdb`.`pingmax`, `corp_pingdb`.`pingdev`, `corp_pingdb`.`pingloss`, `corp_pingdb`.`lastupdated` FROM corp_pingdb, (SELECT hostname, MAX(`lastupdated`) as max_lastupdated FROM `switchbase`.`corp_pingdb` GROUP BY `hostname` ORDER BY hostname asc) b WHERE (`corp_pingdb`.`hostname` = b.hostname AND `corp_pingdb`.`lastupdated` = b.max_lastupdated) ORDER BY `corp_pingdb`.`hostname` Quote Link to comment Share on other sites More sharing options...
fenway Posted January 20, 2010 Share Posted January 20, 2010 That's pretty much the only way to do it... as for the slowness, I'd have to see EXPLAIN output. Quote Link to comment Share on other sites More sharing options...
Roq Posted January 20, 2010 Author Share Posted January 20, 2010 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 73 Using temporary; Using filesort 1 PRIMARY corp_pingdb ALL NULL NULL NULL NULL 166695 Using where 2 DERIVED corp_pingdb ALL NULL NULL NULL NULL 166695 Using temporary; Using filesort That what you're looking for? Quote Link to comment Share on other sites More sharing options...
fenway Posted January 21, 2010 Share Posted January 21, 2010 Yikes.. why are there no keys on any table? Quote Link to comment Share on other sites More sharing options...
Roq Posted January 21, 2010 Author Share Posted January 21, 2010 Because I'm a noobie What would you suggest? Tangent: I'm a network engineer that dabbles in coding. I know enough to automate some tasks and recently decided to take a bunch of my perl / php scripts and just make a simple website to display the results or use some forms instead of cli. This is really the first time that I've ran into performance issues since most of my scripts / tables are very simple and small. Example: Inventory list generated by SNMP queries of serial numbers, model number, hostname, snmp location, etc. Maybe 300 rows or something. Most of my tables are between 50 - 4000 rows. This is the only table I have that is ever-growing which means now is when I have a need to learn about optimizations I'm 100% selftaught based on needs, and now that I have one I need to learn, hah. Quote Link to comment Share on other sites More sharing options...
Roq Posted January 22, 2010 Author Share Posted January 22, 2010 I put an index on hostname and lastupdated and that improved the query a ton (explain shows only one 'all' query, and the otehr one is a single). Didn't think it would be that easy hah. Thanks for your help I'm going to mark this one as solved, for now Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.