Welcome to WebmasterWorld Guest from 126.96.36.199
Forum Moderators: open
I'm got tables created for each day, 20080501,20080502,20080503,etc. All contain 3 columns with the same names. I want to count the total number of occurances across multiple tables and get the top results.
20080501 20080502 20080503
-------- -------- --------
c1 c2 c3¦¦¦¦c1 c2 c3 ¦¦¦¦c1 c2 c3
1 a red ¦¦¦¦1 d green¦¦¦¦1 a yellow
2 b blue¦¦¦¦2 e red ¦¦¦¦2 c red
3 c red ¦¦¦¦3 f blue ¦¦¦¦3 f red
So I want a result like:
I've tried something like...
select column3, COUNT(*) as Number from '20080501` GROUP BY column3 union
select column3, COUNT(*) as Number from `20080502` GROUP BY column3 union
select column3, COUNT(*) as Number from '20080503` GROUP BY column3 ORDER by Number desc limit 20;
But that doesn't combine the totals. Can anyone shed some light?
Each table will have about 50000 entries and I want to keep 6 months worth of data. Can MySQL handle a table with 9 million rows well?
I'm a SQL*Server guy, but if MySQL can't handle 9 million rows then a) you're using the wrong DB or b) you might consider trimming back the amount of data you keep or archive off old data monthly to another instance of MySQL.
If you insist on using daily tables as you've described above, you might consider having a summary table with a transaction date, a value for field c3, and a count of how many rows appeared in the daily table for field c3. You could schedule a job to run after midnight to summarize the previous day's data and append the daily results to the summary table. So in the above example one 20080504 the summary table would look like:
Now you have summary counts by color by day and everything is in a single table. Using this same table you can get them by week, month, or any date range you'd like by querying one table with a simple query.
[edited by: ZydoSEO at 6:00 pm (utc) on May 11, 2008]
I suggest you create a small script to generate a test dataset with the max number of records you expect. Then play around with queries and observe performance. The explain command should help you understand what indexes you need.
[edited by: Gorilla at 6:13 pm (utc) on May 11, 2008]
If the queries start to take too long I may just create temp tables with daily stats.
Thanks for all your help.