[phpOpenTracker-general] Wondering about optimisations of phpOpenTracker

Hello,

I was asked by one of my clients to optimise their statistic systems,=20
based on phpOpenTracker ver 1.2.0 for their 12 web sites.
The current system is running since august, 2003 and the database has=20
the following entries numbers on the different tables :
pot_accesslog : 763.000 entries
pot_referers : 265.000 entries
pot_add_data : 394.000 entries
pot_visitors : 394.000 entries

I don't know if it is a lot of data for this system or not. I do not=20
hope so...

the main problem is that the system is very slow : it takes about 3=20
minutes to give the main statistics with the simple_report page, shipped=20
with the package.

PhpOpenTracker is running on a 2.4.18 Suse (Its a Pentium III with 512=20
Mb), MySQL 4.0.20 and php 5.04.

So, I read the doc, mostly the performance tuning part, and I configured=20
the mysql_merge db handler.
(As I alredy had existing pot_accesslog and pot_visitors tables, I wrote=20
a little script that creates the corresponding pot_accesslog_yyyymm and=20
pot_visitors_yyyymm tables according to the timestamp column, then I=20
created the pot_accesslog and pot_visitors merge tables by hand, by=20
looking in ).
It did not really changed the performances of the system. I alos=20
modified some of the config parameters, like cache configuration.

When I turned debug mode to on (mode 2), I looked at the different=20
queries made on the database (they are sown up on the statistics web page=
).
Look at his one :

SELECT COUNT(*) AS result
FROM pot_accesslog accesslog,
pot_visitors visitors
WHERE visitors.client_id    =3D '1'
AND visitors.accesslog_id =3D accesslog.accesslog_id


A cartesian product is made (FROM pot_accesslog, pot_visitors) instead=20
of making a JOIN clause. We could rewrite the query like this and get=20
the same result :

SELECT COUNT(*) AS result
FROM pot_accesslog accesslog

INNER JOIN pot_visitors visitors
	ON visitors.accesslog_id =3D accesslog.accesslog_id
WHERE visitors.client_id    =3D '1'


There are a lot of queries that make a cartesian product instead of=20
joining tables. Isn'nt it the cause of a great memory consumption ?
If you think that this post should appear in the devel ml, tell me so.

I am searching for other ways to optimise the system, if you have any=20
clues of how I could to that, I would be glad to hear from you.

Best regards,

L=E9o.