I’m using the WordPress plugin StatSurfer for quick dashboard statistics, but it’s getting slower and slower while my blog is getting older.
Here is one fact about mySQL: 90% of all speed problems could be solved simply by adding indices, maybe more.
It’s not mySQLs fault but a really common mistake of mySQL users to forget adding an index or two on a new table. Why should they – an empty table is really fast.
My wp_StatSurfer table has 641.531 rows (which isn’t much for a database, but for mySQL) and the table layout looks really bad:
mysql> DESC wp_StatSurfer; +--------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+----------------+ | id | mediumint(9) | NO | PRI | NULL | auto_increment | | date | tinytext | YES | | NULL | | | time | tinytext | YES | | NULL | | | ip | tinytext | YES | | NULL | | | urlrequested | text | YES | | NULL | | | agent | text | YES | | NULL | | | referrer | text | YES | | NULL | | | search | text | YES | | NULL | | | nation | tinytext | YES | | NULL | | | os | tinytext | YES | | NULL | | | browser | tinytext | YES | | NULL | | | searchengine | tinytext | YES | | NULL | | | spider | tinytext | YES | | NULL | | | feed | tinytext | YES | | NULL | | | user | tinytext | YES | | NULL | | | timestamp | tinytext | YES | | NULL | | | threat_score | smallint(6) | YES | | NULL | | | threat_type | smallint(6) | YES | | NULL | | +--------------+--------------+------+-----+---------+----------------+ 18 rows in set (0.01 sec)
A lot of tinytext and text columns. Why? Did the developer really expect an ip address to become 2 GB long?
I don’t want to change the table layout right now, but add some indices depending on the slow queries I saw in SHOW FULL PROCESSLIST\G while my slow dashbord was loading:
mysql> ALTER TABLE wp_StatSurfer ADD INDEX idx_ip (ip(45)), ADD INDEX idx_d_f_s_i (date(19),ip(45),feed(16),spider(16));
It took 7 seconds to create those indices but my dashboard is now loading in 10 to 15 seconds instead of over a minute without the indices.
It’s still much slow than neccessary (and maybe I’ll change the column types one day) but a lot faster than it used to be.
