Multiple tables for same data - how to sort

[Hans]

Hi

We are developing an application that stores logging data in a mysql
database.
Due to some facts, we decided to store the loggingdata in a seperate table
for each day. ie we have table Log_20060101, log_20060102 etc. They all have
ofcourse the same structure.\
This is the structure:
Id, integer
Time, char(26)
Source, char(10)
Destination, char(10)
Data, char(20)

The ID field is unique for all dates, as it is calculated from the current
time.

The front-end application, that displays the data, intially shows the data,
sorted by Time, descending. Thus it starts with the latest entry and wandes
through today's table until the screen is full, and if the screen is not
full yet, it goes to yesterday's table etc.

The Source and Destination fields are spread randomly through all the
tables, so if the user chooses to sort the display based on Source or
Destination, the application basically has to browse throug *all* the tables
and then start with the lowest (or highest, whatever direction the sort is
in) Source field. So it could well be that the first Source is in table
Log_20060101 and the next in Log_20060606 and then again in Log_20060101
etc.
(As in
SELECT * FROM <All tables>
ORDER BY Source
LIMIT 40;
)

Even more troublesome is it when the user slides the scrollbar-slider down,
so the app has to find n fields, starting from the m-th.
(As in
SELECT * FROM <All tables>
ORDER BY Source
LIMIT 40
OFFSET 500000;
)

The datasize is dependent on the user's specific situation, but typically
30.000 records per day.
That would be > 10.000.000 per year

Can anyone point me in the right direction as to how this search can be done
the fastest way.

Kind regards, Hans

[Gordon Burditt]

>We are developing an application that stores logging data in a mysql

>database.
>Due to some facts, we decided to store the loggingdata in a seperate table
>for each day. ie we have table Log_20060101, log_20060102 etc. They all have
>ofcourse the same structure.\

This is almost always a mistake. Your questions illustrate why.
You're usually better putting it all in one table, with the variable
table name (or the variable portions of it) put in an added column
if that info isn't there already.

In your example, do you even have ANY queries that will be restricted
to a single table (known in advance BEFORE you start counting
how many entries you retrieved)?

>This is the structure:
>Id, integer
>Time, char(26)
>Source, char(10)
>Destination, char(10)
>Data, char(20)
>
>The ID field is unique for all dates, as it is calculated from the current
>time.
>
>The front-end application, that displays the data, intially shows the data,
>sorted by Time, descending. Thus it starts with the latest entry and wandes
>through today's table until the screen is full, and if the screen is not
>full yet, it goes to yesterday's table etc.
>
>The Source and Destination fields are spread randomly through all the
>tables, so if the user chooses to sort the display based on Source or
>Destination, the application basically has to browse throug *all* the tables
>and then start with the lowest (or highest, whatever direction the sort is
>in) Source field. So it could well be that the first Source is in table
>Log_20060101 and the next in Log_20060606 and then again in Log_20060101
>etc.
>(As in
>SELECT * FROM <All tables>
>ORDER BY Source
>LIMIT 40;
>)
>
>Even more troublesome is it when the user slides the scrollbar-slider down,
>so the app has to find n fields, starting from the m-th.
>(As in
>SELECT * FROM <All tables>
>ORDER BY Source
>LIMIT 40
>OFFSET 500000;
>)
>
>The datasize is dependent on the user's specific situation, but typically
>30.000 records per day.
>That would be > 10.000.000 per year
>
>Can anyone point me in the right direction as to how this search can be done
>the fastest way.

Use a single table. Have a date-time field and index it.

Gordon L. Burditt

[Hans]

"Gordon Burditt" <gordonb.lihe9@burditt.org> wrote in message
news:128b4j9a5tmjp38@corp.supernews.com...

> >We are developing an application that stores logging data in a mysql
>>database.
>>Due to some facts, we decided to store the loggingdata in a seperate table
>>for each day. ie we have table Log_20060101, log_20060102 etc. They all
>>have
>>ofcourse the same structure.\

>

> This is almost always a mistake. Your questions illustrate why.
> You're usually better putting it all in one table, with the variable
> table name (or the variable portions of it) put in an added column
> if that info isn't there already.

We did that, we indexed the Date and Time fields, and also the Source and
Destination fields. It appeared that, when the table had like 10.000.000
rows and the user
wanted something like
SELECT *
FROM Table
LIMIT 40
OFFSET 5000000

(as a result of repositioning the scrollbar slider), this takes too long to
have an
acceptable response time (over 30 seconds), this is simply unacceptable.
Besides that, when the user applies a filter to show only records where
Source = 'SomeSource', then to find the number of matched records (to set
the scrollbar's parameters) takes too long and also the query to find the
records just takes too long to be convenient for the user.
(Obviously we have the proper indexes applied and tried various combinations
of indexes; there's no gain possible here imho)

That's why we chose this approach. While the application starts, all tables
are queried with

SELECT Source, Destination, COUNT(*) FROM <Table>
GROUP BY Source, Destination

to find all possible combinations and their respective counts, and store
this in an std::vector, so that at least the counts (for the Scrollbar
parameters) are found with an almost unnoticable delay.

Most of the functionalty is alread implemented, and it is working like a
charm, must faster than with all data in one table.

An additional advantage is the support for our backup/archiving system, we
choose ( a long time ago, and that's not gonna change) to create one
backup-file every day at midnight, with all changes of the last 24 hours.
With the one-table-per-day solution, we just tarball the table-files instead
of using mysqldump.

The only problem is to find the records when the user chooses to sort on the
'Source' or 'Destination' field.
It is not for sure yet that this feature will be implemented, it will only
be implemented if any of you guys help me find a proper solution, with an
acceptable response time.

>
> In your example, do you even have ANY queries that will be restricted
> to a single table (known in advance BEFORE you start counting
> how many entries you retrieved)?

Yes, there are. Normally, the application 'follows' today's table, (as in
tail -f ) with a selectable refresh timeout (30 secs up to 1 hour, or
disabled)

>

>>This is the structure:
>>Id, integer
>>Time, char(26)
>>Source, char(10)
>>Destination, char(10)
>>Data, char(20)
>>
>>The ID field is unique for all dates, as it is calculated from the current
>>time.
>>
>>The front-end application, that displays the data, intially shows the
>>data,
>>sorted by Time, descending. Thus it starts with the latest entry and
>>wandes
>>through today's table until the screen is full, and if the screen is not
>>full yet, it goes to yesterday's table etc.
>>
>>The Source and Destination fields are spread randomly through all the
>>tables, so if the user chooses to sort the display based on Source or
>>Destination, the application basically has to browse throug *all* the
>>tables
>>and then start with the lowest (or highest, whatever direction the sort is
>>in) Source field. So it could well be that the first Source is in table
>>Log_20060101 and the next in Log_20060606 and then again in Log_20060101
>>etc.
>>(As in
>>SELECT * FROM <All tables>
>>ORDER BY Source
>>LIMIT 40;
>>)
>>
>>Even more troublesome is it when the user slides the scrollbar-slider
>>down,
>>so the app has to find n fields, starting from the m-th.
>>(As in
>>SELECT * FROM <All tables>
>>ORDER BY Source
>>LIMIT 40
>>OFFSET 500000;
>>)
>>
>>The datasize is dependent on the user's specific situation, but typically
>>30.000 records per day.
>>That would be > 10.000.000 per year
>>
>>Can anyone point me in the right direction as to how this search can be
>>done
>>the fastest way.

>

> Use a single table. Have a date-time field and index it.

been there, did that, got the t-shirt, but this is not the proper solution.
Thanks for the advise though.

>
> Gordon L. Burditt