Fred Leeflang - 2006-09-05

Logged In: YES
user_id=48847

This seems to be caused by a large amount of rows in the
database. For example the query for the Event Listing does a
join of three database tables, ArpEvents, EventTypes and
EventDefinitions. The latter two tables are not large by any
means so the join makes no big difference here.

The first table, ArpEvents, however, has:

mysql> select count(*) from ArpEvents;
+----------+
| count(*) |
+----------+
| 613437 |
+----------+

This is not really that much it would seem.

Further analyses reveals:

usertracking:~# mysqladmin -p -uroot processlist
+-----+--------+-----------+--------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time |
State | Info
|
+-----+--------+-----------+--------------+---------+------+------------------------------+------------------------------------------------------------------------------------------------------+
| 119 | utuser | localhost | usertracking | Query | 58 |
Copying to tmp table on disk | SELECT
a.id,date_format(a.t,'%r %b %D') as
t,a.subject,e.type,a.mac,a.ip,s.label FROM ArpEvents a, E |
| 120 | utuser | localhost | usertracking | Sleep | 1352 |
|
|
| 122 | utuser | localhost | usertracking | Sleep | 299 |
|

So the query:

SELECT COUNT(a.t) AS count FROM ArpEvents a, EventTypes
e,EventDefinitions s WHERE a.event_type = e.id AND
a.eventdefinition = s.id

is the (or part of the) real problem