Update on /referrers performance

hns, April 24, 2002 at 10:24:53 AM CEST

I just played around with the antville database and noticed that it looks like there are no indices at all on the ACCESS table! (Except for the primary key, that is.) For example, simply querying for Access data for one weblog takes 10 seconds:

mysql> select count(*) from ACCESS where WEBLOG_ID=1;
+----------+
| count(*) |
+----------+
|     1614 |
+----------+
1 row in set (10.25 sec)

whereas a similar query takes 0.1 seconds on the TEXT table, which has an index on WEBLOG_ID:

mysql> select count(*) from TEXT where WEBLOG_ID=1;
+----------+
| count(*) |
+----------+
|       69 |
+----------+
1 row in set (0.01 sec)

This suspicion is also consistent with the antville_indexes.sql file which doesn't have any entries for the ACCESS table.

I propose we add indices to the relevant columns of the ACCESS table. If that doesn't help enough, we can still delete older records, but I think if we are able to keep all the access history in DB, that would be even better.

PS: for another cool Ausbaustufe of Referrer-Logs have a look at www.disenchanted.com (via LNGR). This is a per-page referrer tracker that really implements bidirectional links. Interestingly, it seems to also have to solved a performance problem because the referrers page is broken down into smaller portions. I think it wouldn't be too hard to implement this for antville if we added the target URL, or maybe better the target story id, to the ACCESS table and a listReferrers macro to the story prototype... just thinking out loud...

PPS: I just noticed that the table definition for ACCESS has a lot of mediumtext columns:

#----------------------------
# Table structure for ACCESS
#----------------------------

create table ACCESS (
   ID bigint(20) not null auto_increment,
   WEBLOG_ID bigint(20),
   REFERRER mediumtext,
   IP mediumtext,
   URL mediumtext,
   PATH mediumtext,
   `ACTION` mediumtext,
   BROWSER mediumtext,
   `DATE` datetime,
   unique ID (ID)
);

Is this really a good thing performance- and space-wise? wouldn't it be better to have varchar(256) and varchar(512) in most places?

comment

tobi, April 24, 2002 at 11:04:31 AM CEST

index? ok!

thanks hannes for your explorations. obviously i did not care about indexing the access table because of lack of knowledge...

robert gave me some advice now and i would add indexes by issueing the following sql statements:

create index IDX_WEBLOG_ID on ACCESS (WEBLOG_ID); create index IDX_DATE on ACCESS (DATE); create index IDX_REFERRER on ACCESS (REFERRER(30));

regarding your question about mediumtext columns i guess the answer must be no and so i would change the ones for IP, ACTION, PATH and BROWSER to varchar columns, too.

URL and REFERRER i would like to leave with a bigger size because urls can easily become longer than 255 chars. i don't think varchar(512) is valid sql, that's why it should ~~remain mediumtext~~ be text, shouldn't it?

ad ausbaustufe: sounds and looks interesting but i have to take some time and a closer look to understand what it's all about. thanks for the link, anyway.

update: ok, it's done. the columns' storage sizes are reduced and the indexes are created.