SMF 1.1 query speedup tip

[Nibbler]

Hi,

While working to reduce mysql usage I noticed that this query was starting to appear in the slow query log (set at 2 seconds):

Code Select Expand


  SELECT COUNT(ID_MSG)
  FROM smf_messages
  WHERE posterTime < N
  AND ID_TOPIC = N


So I extended this key:

Code Select Expand

KEY posterTime (posterTime)

to this:

Code Select Expand

KEY posterTime_topic (posterTime,ID_TOPIC)

And changed the query to a SELECT COUNT(*) so that the new key will be used.

This gave this performance increase on a sample query from:

Code Select Expand


mysql> SELECT COUNT(ID_MSG)
    ->                                 FROM smf_messages
    ->                                 WHERE posterTime < 1132250283
    ->                                         AND ID_TOPIC = 15;
+---------------+
| COUNT(ID_MSG) |
+---------------+
|         79379 |
+---------------+
1 row in set (1.51 sec)


to

Code Select Expand


mysql> SELECT COUNT(*)
    ->                                 FROM smf_messages
    ->                                 WHERE posterTime < 1132250283
    ->                                         AND ID_TOPIC = 15;
+----------+
| COUNT(*) |
+----------+
|    79379 |
+----------+
1 row in set (0.06 sec)


I hope this is useful to someone.

For reference, the forum is http://www.bettandget.org/forum/ [nofollow] running on MySQL 4.0.25

[Grudge]

Thanks for the tip, we'll look into it. If you have any more suggestions then by our guests

[Joshua Dickerson]

In MySQL 5 it should be able to use the key for ID_TOPIC

[Compuart]

Thanks for posting this suggestion. The query you mentioned and more of these type of queries will be addressed in RC2.