WordPress StatPress
March 16th, 2009
.. has the database table definitions from hell! Just take a look at this:
+--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | id | mediumint(9) | NO | PRI | NULL | auto_increment | | date | text | YES | | NULL | | | time | text | YES | | NULL | | | ip | text | YES | | NULL | | | urlrequested | text | YES | | NULL | | | agent | text | YES | | NULL | | | referrer | text | YES | | NULL | | | search | text | YES | | NULL | | | nation | text | YES | | NULL | | | os | text | YES | | NULL | | | browser | text | YES | | NULL | | | searchengine | text | YES | | NULL | | | spider | text | YES | | NULL | | | feed | text | YES | | NULL | | | user | text | YES | | NULL | | | timestamp | text | YES | | NULL | | +--------------+--------------+------+-----+---------+----------------+
This is horrible database design, and since this table quickly grows in size, this is something to be fixed immediately:
mysql> ALTER TABLE wp_statpress CHANGE date date VARCHAR(8);
mysql> ALTER TABLE wp_statpress CHANGE time time VARCHAR(8);
mysql> ALTER TABLE wp_statpress CHANGE ip ip VARCHAR(16);
mysql> ALTER TABLE wp_statpress CHANGE nation nation VARCHAR(8);
mysql> ALTER TABLE wp_statpress CHANGE timestamp timestamp INT(10) UNSIGNED;
mysql> ALTER TABLE wp_statpress CHANGE user user VARCHAR(32);
mysql> ALTER TABLE wp_statpress CHANGE urlrequested urlrequested VARCHAR(255);
mysql> ALTER TABLE wp_statpress CHANGE referrer referrer VARCHAR(255);
mysql> ALTER TABLE wp_statpress CHANGE browser browser VARCHAR(255);
mysql> ALTER TABLE wp_statpress CHANGE searchengine searchengine VARCHAR(64);
mysql> ALTER TABLE wp_statpress CHANGE spider spider VARCHAR(64);
mysql> ALTER TABLE wp_statpress CHANGE feed feed VARCHAR(255);
mysql> ALTER TABLE wp_statpress CHANGE agent agent VARCHAR(255);
mysql> ALTER TABLE wp_statpress CHANGE search search VARCHAR(255);
mysql> ALTER TABLE wp_statpress CHANGE os os VARCHAR(64);
mysql> ALTER TABLE wp_statpress CHANGE time time VARCHAR(8);
mysql> ALTER TABLE wp_statpress CHANGE ip ip VARCHAR(16);
mysql> ALTER TABLE wp_statpress CHANGE nation nation VARCHAR(8);
mysql> ALTER TABLE wp_statpress CHANGE timestamp timestamp INT(10) UNSIGNED;
mysql> ALTER TABLE wp_statpress CHANGE user user VARCHAR(32);
mysql> ALTER TABLE wp_statpress CHANGE urlrequested urlrequested VARCHAR(255);
mysql> ALTER TABLE wp_statpress CHANGE referrer referrer VARCHAR(255);
mysql> ALTER TABLE wp_statpress CHANGE browser browser VARCHAR(255);
mysql> ALTER TABLE wp_statpress CHANGE searchengine searchengine VARCHAR(64);
mysql> ALTER TABLE wp_statpress CHANGE spider spider VARCHAR(64);
mysql> ALTER TABLE wp_statpress CHANGE feed feed VARCHAR(255);
mysql> ALTER TABLE wp_statpress CHANGE agent agent VARCHAR(255);
mysql> ALTER TABLE wp_statpress CHANGE search search VARCHAR(255);
mysql> ALTER TABLE wp_statpress CHANGE os os VARCHAR(64);
If you’re using MySQL 5+, you might consider changing the VARCHAR(255) definitions to VARCHAR(1024).
The new definition looks like this:
+--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+------------------+------+-----+---------+----------------+ | id | mediumint(9) | NO | PRI | NULL | auto_increment | | date | varchar(8) | YES | | NULL | | | time | varchar(8) | YES | | NULL | | | ip | varchar(16) | YES | | NULL | | | urlrequested | varchar(255) | YES | | NULL | | | agent | varchar(255) | YES | | NULL | | | referrer | varchar(255) | YES | | NULL | | | search | varchar(255) | YES | | NULL | | | nation | varchar(8) | YES | | NULL | | | os | varchar(64) | YES | | NULL | | | browser | varchar(255) | YES | | NULL | | | searchengine | varchar(64) | YES | | NULL | | | spider | varchar(64) | YES | | NULL | | | feed | varchar(255) | YES | | NULL | | | user | varchar(32) | YES | | NULL | | | timestamp | int(10) unsigned | YES | | NULL | | +--------------+------------------+------+-----+---------+----------------+
You won’t believe this, but they didn’t add any indexes. To prevent your system from going into a coma when doing household on hit data, add at least an index on date:
ALTER TABLE wp_statpress ADD INDEX(date);
Thanks for the heads up, this worked a treat on our blog. This plugin also builds a lot of data but the option to delete that data at regular intervals does not seem to work.