1 (edited by lament 2006-07-07 23:54)

Topic: "query running that was way more innefficient than it had to be"

So I just got an email from my hosting company:

Please be sure to index your tables properly - you had a query running
that was way more innefficient than it had to be - it went from checking
over 2000 entries each time to under 25:

mysql> explain SELECT * FROM 20050910_punbb_messages WHERE owner=65 AND
status=0 ORDER BY posted DESC LIMIT 0,10;
+----+-------------+-------------------------+------+---------------+----
--+---------+------+------+-----------------------------+
| id | select_type | table                   | type | possible_keys | key
| key_len | ref  | rows | Extra                       |
+----+-------------+-------------------------+------+---------------+----
--+---------+------+------+-----------------------------+
|  1 | SIMPLE      | 20050910_punbb_messages | ALL  | NULL          |
NULL |    NULL | NULL | 2002 | Using where; Using filesort |
+----+-------------+-------------------------+------+---------------+----
--+---------+------+------+-----------------------------+
1 row in set (0.02 sec)

mysql> create index owner on 20050910_punbb_messages(owner(10));
ERROR 1089 (HY000): Incorrect sub part key; the used key part isn't a
string, the used length is longer than the key part, or the storage
engine doesn't support unique sub keys
mysql> create index owner on 20050910_punbb_messages(owner);
Query OK, 2002 rows affected (5.08 sec)
Records: 2002  Duplicates: 0  Warnings: 0

mysql> create index status on 20050910_punbb_messages(status(10));
ERROR 1089 (HY000): Incorrect sub part key; the used key part isn't a
string, the used length is longer than the key part, or the storage
engine doesn't support unique sub keys
mysql> create index status on 20050910_punbb_messages(status);
Query OK, 2002 rows affected (8.08 sec)
Records: 2002  Duplicates: 0  Warnings: 0

mysql> explain SELECT * FROM 20050910_punbb_messages WHERE owner=65 AND
status=0 ORDER BY posted DESC LIMIT 0,10;
+----+-------------+-------------------------+------+---------------+----
---+---------+-------+------+-----------------------------+
| id | select_type | table                   | type | possible_keys | key
| key_len | ref   | rows | Extra                       |
+----+-------------+-------------------------+------+---------------+----
---+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | 20050910_punbb_messages | ref  | owner,status  |
owner |       4 | const |   21 | Using where; Using filesort |
+----+-------------+-------------------------+------+---------------+----
---+---------+-------+------+-----------------------------+
1 row in set (0.01 sec)

---

That dropped the load on your database server quite a bit!

Can someone translate this?  Was this a punbb issue or something I need to be doing?  Thanks!

oh, by the way.. the reason the tables are prefixed with 20050910 is because my punbb was hacked (back when everyone was getting hacked last year) and my host restored everything this way.

could it be possible that when they restored the db last year it messed up some indexing?

Re: "query running that was way more innefficient than it had to be"

Sounds like they just created an index for your private messages?

Re: "query running that was way more innefficient than it had to be"

Yes, that's what they did. They added an index to one of the database tables for your private messages mod. This should probably be included by default in the mod.

"Programming is like sex: one mistake and you have to support it for the rest of your life."