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