Speed up Wordpress StatSurfer plugin

Dieser Post wurde aus meiner alten WordPress-Installation importiert. Sollte es Darstellungsprobleme, falsche Links oder fehlende Bilder geben, bitte einfach hier einen Kommentar hinterlassen. Danke.

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)

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'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.