I'm coding statics script with PHP and mySQL. I have started to study mySQL just a while ago so I'm not an expert, yet. The script shows different kind of stats, for example used browsers and operating systems. I use followind kind of SQL query to seek for example used browsers:
SELECT browser, COUNT(*) AS count FROM log GROUP BY browser ORDER BY count DESC
For each stat (6 in total) I use SQL query like above. That means mySQL has to read six times throught the table and it is slow with big table (about 22.000 entries takes about 7 seconds). Now, I have wondered is it possible to combine all those six SQL queries into one query. In other words, one SQL query would seek for agent,os,browser,host,country,referer and count how many visitor has used it, for example: how many has used (browser) Netscape 6 and IE5.
Below is my database structure if it helps.
CREATE TABLE log (
id int(8) DEFAULT '0' NOT NULL AUTO_INCREMENT,
year int(4) NOT NULL,
month int(2) NOT NULL,
day int(2) NOT NULL,
hour int(2) NOT NULL,
time TIME,
agent VARCHAR(100),
os VARCHAR(50),
browser VARCHAR(50),
host VARCHAR(100),
country VARCHAR(11),
referer VARCHAR(200),
PRIMARY KEY (id),
KEY year (month),
KEY month (month)
);
Thanks for your co-operation!
