Creating a bar statistic with php/gd/mysql

[Anon]

hello

I want to create a bar-graph from a mysql table, the table consists of over 5 million entries. for each row i need to fetch around 5 fields, which gives me around 25'000'000 datasets (+10'000 / day). I'm wondering how fast the creation of the bars will be, and if there's any sense in creating the statistics dynamically or if I should write some perl script that runs each night and creates an image, which then would be faster for the user to see.

Anyone has any experience with this, speed issues, some hints about easily doing this and so on.

Thanks and gretings

sweeper

[Anon]

How fast it will be depends on how long it takes to fetch your data, and how big the final image will be.

How many images, how much data per image, how many bars per image etc...

I have created images at 1024/768 filled with about 900 records out of a database in less than a second. (single p2-300Mhz 128RAM Linux,mysql on same server)

[Anon]

I guess the bottleneck here is that fetching the data from mysql takes a too long time. through the mysql client i've sent the following query:

select date_time from traffic_log order by date_time desc limit 1000;

the query took 6mins27seconds to complete... (p2/450 256megs ram)

this will most probably lead to an apache-timeout even when I just select some rows, as above. guess I'll have to fiddle around with the database entries to speed everything up.

-swp

[Anon]

I assume you have added indexes to your database, set the database server params correctly etc?

If you are going to make reports on surtain timeframes it might be a good idea to first copy the data for that timeframe to a new table before doing multiple queries on it.