Question Disable Hottest Thread or optimize query

[janklerks]

We've got a pretty large forum. After about 4 months of having the thanks hack installed (and trimmed down so it's basically a "like" button for posts), we have about 1.45 million rows in our dbtech_thanks_entry table.

I've had to add quite a few indexes to keep things speedy, but I have one final (hopefully) query that's absolute murder: The query when people visit the Hottest posts/threads page.

# Query_time: 14.674804  Lock_time: 0.000077  Rows_sent: 25  Rows_examined: 4914866  Rows_affected: 0  Rows_read: 4914866
# Bytes_sent: 6424
SET timestamp=1368192326;
SELECT
                                COUNT(*) AS entrycount,
                                post.title AS posttitle,
                                thread.title AS threadtitle,
                                thread.title AS title,
                                post.postid,
                                thread.forumid
                        FROM dbtech_thanks_entry AS entry
                        LEFT JOIN post AS post ON(post.postid = entry.contentid)
                        LEFT JOIN thread AS thread ON(thread.threadid = post.threadid)
                        WHERE contenttype = 'post'
                                AND varname = 'likes'


                        GROUP BY entry.contentid
                        ORDER BY entrycount DESC
                        LIMIT 25;

I've indexed it to the best of my ability, but it's still scanning 700,000 rows, writing to a temporary table, and doing a filesort. Even on the beefy system we're running, it takes around 10 seconds.


Fortunately the page isn't hit very often, but it has caused a few issues when piled on top of other problems. Is there an easy way to disable the "hottest threads" page? I couldn't find a switch or anything to disable it.



Also, in case you're wondering what I've done to the dbtech_thanks_entry table, aside from converting it to InnoDB, here's a full list of the indexes:

mysql> show indexes from dbtech_thanks_entry;
+---------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table               | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dbtech_thanks_entry |          0 | PRIMARY        |            1 | entryid        | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid        |            1 | userid         | A         |       30979 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid        |            2 | entryid        | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_2      |            1 | receiveduserid | A         |       77447 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_2      |            2 | entryid        | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | receiveduserid |            1 | receiveduserid | A         |       77447 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid         |            1 | userid         | A         |       60611 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid_2       |            1 | userid         | A         |       58085 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid_2       |            2 | contenttype    | A         |       60611 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid_2       |            3 | dateline       | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_3      |            1 | varname        | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_3      |            2 | contenttype    | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_3      |            3 | entryid        | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | contenttype    |            1 | contenttype    | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | contenttype    |            2 | contentid      | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | varname        |            1 | varname        | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | varname        |            2 | contenttype    | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | varname        |            3 | contentid      | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | contenttype_2  |            1 | contenttype    | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | contenttype_2  |            2 | dateline       | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
+---------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
20 rows in set (0.06 sec)

(It's probably overkill but I error on the side of INDEX ALL THE THINGS!)

 

[Fillip H.]

Fortunately the page isn't hit very often, but it has caused a few issues when piled on top of other problems. Is there an easy way to disable the "hottest threads" page? I couldn't find a switch or anything to disable it.

Your only option is to remove the entry from the navbar unfortunately, in a future version I'll look into adding an option to disable the page altogether.

With the knowledge I have today, I can't see a way of optimising this query - no amount of indexing will help. Aggregate values with GROUP BY and WHERE conditions are notoriously difficult to optimise.

Also, in case you're wondering what I've done to the dbtech_thanks_entry table, aside from converting it to InnoDB, here's a full list of the indexes:

mysql> show indexes from dbtech_thanks_entry;
+---------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table               | Non_unique | Key_name       | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dbtech_thanks_entry |          0 | PRIMARY        |            1 | entryid        | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid        |            1 | userid         | A         |       30979 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid        |            2 | entryid        | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_2      |            1 | receiveduserid | A         |       77447 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_2      |            2 | entryid        | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | receiveduserid |            1 | receiveduserid | A         |       77447 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid         |            1 | userid         | A         |       60611 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid_2       |            1 | userid         | A         |       58085 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid_2       |            2 | contenttype    | A         |       60611 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | userid_2       |            3 | dateline       | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_3      |            1 | varname        | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_3      |            2 | contenttype    | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | entryid_3      |            3 | entryid        | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | contenttype    |            1 | contenttype    | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | contenttype    |            2 | contentid      | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | varname        |            1 | varname        | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | varname        |            2 | contenttype    | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | varname        |            3 | contentid      | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | contenttype_2  |            1 | contenttype    | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| dbtech_thanks_entry |          1 | contenttype_2  |            2 | dateline       | A         |     1394059 |     NULL | NULL   |      | BTREE      |         |               |
+---------------------+------------+----------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
20 rows in set (0.06 sec)

(It's probably overkill but I error on the side of INDEX ALL THE THINGS!)

While I'm no MySQL expert, I do know that over-indexing is a monumentally bad idea because it slows down inserts by a large factor. Every time you insert a new row, you now have to update 20 indexes.
If we go purely by the cardinality - which may be the wrong metric to calculate how many rows are written to file, admittedly - every time you insert a row into this table you would be checking and potentially writing 11,517,760 rows.

Here's a short blog post as to why it's a bad idea - the first result on Google for "mysql over-indexing": MySQL Optimization » Why not to index everything in a MySQL Table ( Costs of Over-Indexing in MySQL )
I'm sure there's plenty of more in-depth explanations of why it shouldn't be done by reading further down the results - I happened to pick that post because I recognise the domain name from Googling for query optimisation tips while working on this mod

When optimising MySQL, rewriting the queries is going to be the best option. If that's not feasible / you can't find a way to optimise the queries, indexes should be tested against the queries ran.
Add index -> Optimize table -> Inspect the query with EXPLAIN.
Does it say it's hit the new index? If yes, pat yourself on the back for an index well constructed.
If not, drop index and GOTO 10.

Have you tested all those indexes and found that each and every one of them is required for one query or another? This post: MySQL Optimization » Leftmost Prefixes with MySQL Indexes may help.
IIRC I write all my queries with more or less the same order of WHERE conditions, so I'm sure you could get away with less indexes by using composite indexes more efficiently.

Hope this helps

 

[janklerks]

The inserts aren't an issue (yet). Most of the indexes I added were in response to slower queries, and they made a remarkable increase in speed once they were added.

It's on my list of things to test though,


Thanks!