Real problem !

[Anon]

I wrote a php/mysql based banners serving application.
When a banner is viewed or clicked, the app log the event in a mysql table called "LOG".

I'm using this same table to display clients stats.

LOG table contains (ip,browser,time,date,viewer,banner,client...)

each feild is important for reporting

My problem now is : When the application serves millions of banners the "LOG" table will cause delivery slow.
And when I purge data from "LOG" table my clients won't see thier stats data.

Anyone have a great idea to solve this prob ?

Thanks

[[deleted]]

If you are just usingthe LOG table to insert 'banner-views' into, then you will not have a big problem. Only selects get slow on big tables.

[Anon]

Hi vincent

I'm using the "LOG" table to :
store banner views
store banner clicks
and
select data for advertisers reports
select data for publishers reports
select data for campaigns reports
select data for creatives reports

[[deleted]]

In that case I'd recommend that you use InnoDB type tables for your LOG table.
InnoDB has row-level-locking which means that you can do large select-queries without slowing down the inserts.

Another option is to create a 'live' table to store the new clicks, and an 'archive' table that contains all the 'old' data. then once a day you can move all the new clicks to the archive table, so you'll have a very small and fast 'live' table to process banners from the website, and a very large and slower 'archive' table for your reports.

[Anon]

Thanks vincent,

The second option will work fine but my clients won't see thier stats in real time.

InnoDB seems to be the good anti-slow solution, but I've never use it, is there any site that explain the install prosedure on both win2k and linux ?

Do you use it ?
If I want to use it, should I modify all the sql queries ?

[[deleted]]

The MYSQL website has all the information you need regarding InnoDB tables.

PS: windows is not an option for databases servers, only MS thinks it is.