S9Y Tuning

Ist es auch.


Eine Durchsicht der Queries in dem Modul fand auf den ersten Blick diese Vorschläge für Änderungen:

SELECT count(sessID) FROM s9y_visitors WHERE 'session_id())."' = sessID GROUP BY sessID",
=> alter table s9y_visitors add index (sessID)
Was soll das Group By hier bewirken?

SELECT timestamp FROM s9y_entries ORDER BY timestamp ASC limit 1 et al
=> select min(timestamp), max(timestamp), count(entries) from s9y_entries;
statt drei einzelner queries

=> select isdraft, count(isdraft) as anzahl from s9y_entries group by isdraft;
statt zwei einzelner queries

=> select author, count(author) as entries from s9y_entries group by author;
order by ist hier sinnlos aber auch nicht schädlich


In updatestats:

SELECT COUNT(year) AS result FROM s9y_visitors_count
WHERE year='$year' AND month='$month' AND day='$day'"

=> alter table s9y_visitory_count add primary key(year, month, day); -- in dieser Reihenfolge, siehe statistics_getdailystats queries

update s9y_visitors count set hits = hits + 1; and
insert into s9y_visitors_count (year, month, day, visits, hits) values (...., 1, 1);

=> insert into s9y_visitors_count (year, month, day, visits, hits) values (...., 1, 1) on duplicate key set hits = hits + 1;

In updateVisitor:
UPDATE s9y_visitors SET time = '$time', day = '$day'
WHERE sessID = '...';
=> alter table s9y_visitors add index(sess_id);

in countVisitor:

SELECT count(refs) AS referrer FROM s9y_refs WHERE refs = '$urlC' GROUP BY refs
=> alter table s9y_refs drop primary key, drop column id, add primary key(refs)
=> insert into s9y_refs (refs, count) values ("$urlC", 1) on duplicate key update set count = count + 1;

Eigentlich will man hier mit md5(refs) arbeiten. Also

=> alter table s9y_refs change column refs refs varchar(32) not null; -- primary key
=> insert into s9y_refs (refs, count) values (".md5($urlC).", 1) on duplicate key update set count=count+1;


Hier die Tables, wie sie vor den Änderungen definiert werden:

CREATE TABLE s9y_visitors (
counter_id integer unsigned not null primary key auto_increment,
sessID varchar(35) not null default '',
day varchar(10) not null default '',
time varchar(5) not null default '',
ref varchar(255) default null,
browser varchar(255) default null,
ip varchar(15) default null
);


CREATE TABLE s9y_visitors_count (
year int(4) not null,
month int(2) not null,
day int(2) not null,
visits int(12) not null,
hits int(12) not null
);


CREATE TABLE s9y_refs (
id integer unsigned not null primary key auto_increment,
refs varchar(255) not null default '',
count int(11) not null default '0'
);