469,330 Members | 1,212 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

Best Practice Mysql Tables

Hi There,
I've got one table that is constantly being added to (every few
seconds). I've got a bunch of users that need to report from this
table. What's the best way to setup the tables? Create a second table
and updates it with tblLive data every let's say 15 minutes and then
report off of that? Is there a way to synch tables that doesn't have
a lot of over head? Or just report off of tblLive directly? Any
suggestions would be much appreciated.
Regards,
BP Dude Man

Nov 26 '05 #1
3 1539
On 26 Nov 2005 10:03:36 -0800, in mailing.database.mysql "BPDudeMan"
<bp*******@hotmail.com> wrote:
| Hi There,
| I've got one table that is constantly being added to (every few
| seconds). I've got a bunch of users that need to report from this
| table.
What interface (web/desktop app) are you using to display the data?

How frequently does the user need to see this updated information?

What is the range of data required - minutes/hours/days/weeks/months?

Are you displaying raw data or converting to graphs or other display
items?

Do you require comparisons between date/time sets?
| What's the best way to setup the tables? Create a second table
| and updates it with tblLive data every let's say 15 minutes and then
| report off of that? Is there a way to synch tables that doesn't have
| a lot of over head? Or just report off of tblLive directly? Any
| suggestions would be much appreciated.
| Regards,
| BP Dude Man


I would do it the other way - archive your old data (more than 30 days
old) into another table.
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Nov 27 '05 #2
>I've got one table that is constantly being added to (every few
seconds). I've got a bunch of users that need to report from this
table. What's the best way to setup the tables? Create a second table
and updates it with tblLive data every let's say 15 minutes and then
report off of that? Is there a way to synch tables that doesn't have
a lot of over head? Or just report off of tblLive directly? Any
suggestions would be much appreciated.


I'd suggest using the live table directly, if possible.
How serious is it if one of the additions to the table was delayed
for 10 seconds? Do you depend on the server to time-stamp the
entries or do they carry their own time stamps (or are time stamps
not needed)?

Which disrupts the data collection process less, assuming that
the reporting queries get a lock that prevents adding data:
running one of the reports, or grabbing 15 minutes of data (for
moving to a second table)?

How much of the (older) data do you need for one of the reports?

Gordon L. Burditt
Nov 27 '05 #3
BPDudeMan wrote:
Hi There,
I've got one table that is constantly being added to (every few
seconds). I've got a bunch of users that need to report from this
table. What's the best way to setup the tables? Create a second table
and updates it with tblLive data every let's say 15 minutes and then
report off of that? Is there a way to synch tables that doesn't have
a lot of over head? Or just report off of tblLive directly? Any
suggestions would be much appreciated.
Regards,
BP Dude Man


It depends on the nature of the reports. For example, if they
have to be consistent during the course of the day you should
create a report table once a day and use it for all reporting
purposes. If your users want/need more current information then
create a report table once per hour/half-hour/quarter-hour as
need be. BTW it is faster to drop and recreate the report table
rather than update it. Use syntax like:

CREATE reports_table AS SELECT * FROM live_table;

On the other hand, if by reports you really mean queries of the
live database table because "up-to-the-minute" information is
required by the users all reporting should use the live database
table.

The reporting requirements dictate how you should handle the
situation.
HTH

Jerry
Nov 27 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Will Hartung | last post: by
10 posts views Thread by Rich Wallace | last post: by
5 posts views Thread by Ryan Liu | last post: by
51 posts views Thread by bigHairy | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.