Hi all,
I am curious to know if the query times I'm seeing are reasonable or not.
Here's my situation:
I am using a database to keep up with web statistics. The table is currently growing at about 2 million records a day. The sample table I am working with has right at 4 million rows.
When I execute the query below, it takes approximately 8.5 seconds to return. Is this reasonable? Because the way I see it, this time is going to get out of control after a week, or month, or year.
SELECT DATE_FORMAT(time, "%Y-%m-%d %H:%i") AS date, SUM(sbytes) AS bytes FROM log GROUP BY date ORDER BY date
Here is some info about my system: Single Processor Intel Xenon 3.06GHz, 1 GB RAM, RAID 0 SCSI 15K RPM running MySQL ver 11.18 distrib 3.23.58 on RedHat 9.
My table currently looks like this: 3 unsigned ints, 4 char(100), 1 char(15), and a datetime column. I am not using a primary key or indexes.
I'm positive a better table design will drastically improve query time. My concern is the rate at which the time grows. Can someone who has worked with a table of this size let me know what kind of times I should be expecting.
Any information you guys can provide will be greatly appreciated.
Thanks, Dan
Dan,
You don't need the "Order By" since the Group By is already sorting it in that order. As far as speed is concerned, you are duplicating a lot of calculations you've already done the day before. I think you need to store the results that are returned from your daily query, into a summary table (called Summary) so you only need to summarize the current day's raw data once. So just execute your Select statement and append the results to the Summary table, then archive the raw daily data because you won't need it any more (write it to DVD each day or archive it to another database). Do this once a day. So instead of working with 2 million rows a day*365 days=730 million rows, you are only adding 24*60 rows (1 for each minute) to the Summary table * 365 days=525,600 rows = 1388 times faster! Quite an improvement, don't you think? :-)
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
