Mysql. You should never neglect creating additional indexes in table.

Since I’m making statistics engine for Internet-radio ct.fm I have opportunity to play about huge selections and table optiomization.

So, here is a table where all information about channel connections is gathered: ip-address, time of connection, disconnection, conjectural geographical location of user, etc.

create table ctfmstat_log(
lid bigint unsigned primary key auto_increment,
ip int,
start int,
end int,
countrycode varchar(20),
countryname varchar(40),
region varchar(40),
city varchar(40),
latitude float,
longitude float
) DEFAULT CHARSET=utf8;

Typical query to such table is something like “how many users has connected to channel at day n”.

SELECT DATE_FORMAT(FROM_UNIXTIME(start),"%Y%m%d") as day, count(*) as counter 
FROM ctfmstat_log 
WHERE start>1257026400 AND start<1262296800
GROUP BY day ORDER BY day;

I’ve used simple php-code(I don’t bring it here, not so important) that run such query cyclically 10 seconds.

Result: 80-90 fetches

So, let’s change table a little…

create table ctfmstat_log(
lid bigint unsigned primary key auto_increment,
ip int,
start int,
end int,
countrycode varchar(20),
countryname varchar(40),
region varchar(40),
city varchar(40),
latitude float,
longitude float,
index (start)
) DEFAULT CHARSET=utf8;

And here is a result: 8000-8500 fetches!

Fill the difference!

www.iflamberg.net

User login

Mysql. You should never neglect creating additional indexes in table.

Comments

Post new comment

Languages