[mykes]

# Query_time: 977  Lock_time: 455  Rows_sent: 20  Rows_examined: 125192586
SELECT item.item_id, item.item_title, item.threadid, grp.group_title, item.item_
status, item.item_created, item.item_open_until, item.item_pay_after, thread.thr
eadid, thread.forumid, thread.postuserid FROM vbookie_items AS item
                LEFT JOIN vbookie_groups AS grp ON grp.group_id = item.group_id
                LEFT JOIN thread AS thread ON thread.vbookie_item_id = item.item
_id
                WHERE item.item_status='SETTLED'
                ORDER BY  item.item_id DESC 
                LIMIT 0, 20;

vBookie locking the thread table for 455 seconds! The whole site stops for that long. Look at how many rows examined! Dang these LEFT JOINs.

Did some digging:

mysql> select count(*) from thread;
+----------+
| count(*) |
+----------+
| 159281 |
+----------+

mysql> select count(*) from vbookie_items;
+----------+
| count(*) |
+----------+
| 844 |
+----------+

I think it scanned the entire thread table about 844 times to do the query, and it created temporary tables to boot.


My fix was to enter the following query:

create index thread_vbookie on thread (vbookie_item_id);

This costs sizeof(MEDIUM_INT)*159281 bytes of RAM to keep the needed field in RAM all the time.

At MEDIUM_INT = 2 bytes, it's 320K. At 4 bytes, it's 640K. Puny amount of memory to save all those table scans.

I'm pretty sure it worked, as I went to the vbookie.php page and did all kinds of sorts and they were instant.