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: 0mysql> 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: 0mysql> 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?