MySQL count problem

Anon

Hi there,

I'm doing a counter script (for visitor-stats) and I save all info like browser-name, version, etc. in a table called 'stats'. The table has a column named 'browsername' which contains the browser names (duh!). I want to make an overview with the most accessed browsers. I could make a SQL command which shows me the different browser names and the number of times they exist. I wrote the following:

SELECT browsername,count(*) FROM stats GROUP BY browsername

You might say: "What's your problem?". Well, actually there is no problem, but the function is slow (I have a table that grows with an amount of 10.000 records per day, so I have 30.000 records in 3 days and it took me 25 secs to execute the SQL query). Does anyone have a better way to do this?

Greetz,

Vincent Driessen
Venlo, The Netherlands

Anon

Run the script that compiles the results as a periodic job (using cron, on *nix)? You could create a static page from your data say, every fifteen minutes.

This may not be what you want, but supposing you just want the count for each browser type, you could change the SQL to:

"SELECT count(*) from stats where browsername=$foo"

Although I'm not sure that would make a significant difference ...

HTH,

Anon

Log file is only growth up, and the data is never edited. I think if every day, the statistic is calculated and stored on another table, it could be faster.
"SELECT browsername, count(*) from table_1 WHERE theday=... group by browsername"

And by moving past data to archive (after calculated and store the result to another table, just delete those records), your active table could be keep as little as you want.

Anon

The basic theory of a databse is that nothing is stored more than once save IDs, right? Well, make four tables instead of one--
Try it like this--

Table Name: stats
UserID int(5)
BrowserID int(5)
PageID int(5)
Timestamp timestamp(14)

Table Name: IPs
UserID int(5)
IP varchar(20)

Table Name: Browsers
BrowserID int(5)
Browser varchar(50)

Table Name: Pages
PageID int(5)
Page varchar(50)

See how that works? That way you can break apart the data so that you only write the user's IP once, but you can use the 1-to5-byte reference ID to find it. Use the 'left join' SQL statement to compile it all and you'll save a lot of space and not worry about your table exploding to two-hundred megs in a few weeks time.

Anon

Why can't I sort the count(*) row like this?

SELECT browsername,count(*) FROM stats GROUP BY browsername ORDER BY browsername ASC

TIA,

Vincent Driessen
Venlo, The Netherlands

Anon

If you're ordering by browsername, you don't need to group the results, so I don't see why you'd want to.

For pure academic interest, does MySQL give you an error if you issue the query you have, or does it just not do what you expect it to do? If there is an error, what kind of error?