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

re: Best Practice Mysql Tables

On 26 Nov 2005 10:03:36 -0800, in mailing.database.mysql "BPDudeMan"
<bpdudeman@hotmail.com> wrote:
[color=blue]
>| 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.[/color]

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?
[color=blue]
>| 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[/color]

I would do it the other way - archive your old data (more than 30 days
old) into another table.
---------------------------------------------------------------
jnorthau@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

re: Best Practice Mysql Tables

>I've got one table that is constantly being added to (every few[color=blue]
>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.[/color]

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

re: Best Practice Mysql Tables

BPDudeMan wrote:[color=blue]
> 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
>[/color]

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