Comments on: Reducing SlimStat's database size


Klaus's avatarKlaus → http://lellesch.com/

Hi Stephen,

hm ... just tried to reduce the size of the database, but i've got the following message:

ALTER TABLE `slimstat` DROP INDEX `resource_total_uniques`

MySQL meldet:
#1091 - Can't DROP 'resource_total_uniques'. Check that column/key exists

Any clues?

Regards, Klaus

Stephen's avatarStephen

Klaus: I think you can safely ignore that, and any other similar errors that tell you the index doesn't exist. It just means that index wasn't created at setup time. Just run all the other queries and it should be fine.

Klaus's avatarKlaus → http://lellesch.com

That is strange, no matter what querie i do, it alway shows me an error!

"#1091 - Can't DROP 'searchterms_total_uniques'. Check that column/key exists "
and so on ...

Stephen's avatarStephen

OK, then I think it's one of two things... either you haven't done a USE command beforehand to ensure you're using the correct database, or 'slimstat' isn't the correct name for your table.

Try doing "DESCRIBE slimstat" and see what happens. You should get a "no database selected" or "table doesn't exist" error, which should tell you which of the two it is.

Klaus's avatarKlaus → http://lellesch.com/

Zhx a lot for your help, Stephen!

I just have ONE database. so when i do: "DESCRIBE slimstat" - everything is fine.

Im an complete idiot to mysql, but when i have a look at the table slimstat, i recognized, that the names are different.
its name is not "resource_total_uniques"
its name is: "resource_total_uniques_idx"

there is ALWAYS a suffix called "_idx"
does that matter, or does it just mean "index"?

Stephen's avatarStephen

Ahem. That is almost certainly down to me having created my database indexes a long time before I wrote the setup script, and having forgotten that they had different names. Well done for spotting that. :-)

I'll update the instructions with the correct names, but the long and the short of it is that you should delete all the indexes that have the same cardinality as the primary key, whatever their names are.

Klaus's avatarKlaus → http://lellesch.com/

ah - yes, that's it! did it all and everything is fine. the database is nearly half size.

one question, stephen: in your post "Then add new single-field indexes", the field 'resource' is a doublette - the first and the 4th.


Stephen's avatarStephen

So it is. Thanks for letting me know. That's fixed now too.

Jon-Michael's avatarJon-Michael → http://jmdeldin.com

I lost 4MB on the Slimfast diet! :) (My database was only 6.9mb before--no traffic).

I had a quick question on the javascript include. Will it reduce referrer spam?

Thanks for Slimstat too!

Stephen's avatarStephen → http://wettone.com

Jon-Michael: yes, the JavaScript include should help to reduce referrer spam, because most (all?) bots ignore the JavaScript when they request the page.

Robert Wetzlmayr's avatarRobert Wetzlmayr → http://awasteofwords.com/

In my instance, the performance penalties are significant.

Some numbers, based on the average runtime of five iterations for each function (original runtime is 100%):

* Overview: 60% (better)
* Details: 198% (worse)
* Paths: 1.567% (unbelievably worse)

Database size was reduced to approximately 56% of its original size.

Cheers and thanks for your invaluable efforts on this piece,


Robert Wetzlmayr's avatarRobert Wetzlmayr → http://awasteofwords.com/

Oops, sorry. I got the modules wrong. The corrected values are those:

* Paths: 60% (better)
* Overview: 198% (worse)
* Today: 1.567% (unbelievably worse)

About this page

These are comments on the latest entry in Stephen Wettone's weblog. It was published on 20 April 2006.

The previous entry, published on 14 Mar 2006, was SlimStat via JavaScript.

← Mar Apr

Search the weblog

Enter a word or phrase to search my weblog archives: