# MySQL: Are you actually utilizing your indexes?

DevFeed: [MySQL: Are you actually utilizing your indexes?](<https://devfeed.tech/articles/mysql-are-you-actually-utilizing-your-indexes-51972.md>)

Original publisher: [Read original article](<https://lea.verou.me/2010/03/mysql-are-you-actually-utilizing-your-indexes/>)

Author: Lea Verou

Published: 2010-03-25T00:00:00Z

Content type: tutorial

Language: en

Sources: [Lea Verou's blog](<https://devfeed.tech/sources/lea-verou-s-blog.md>)

Topics: [MySQL](<https://devfeed.tech/topics/mysql.md>), [Query (disambiguation)](<https://devfeed.tech/topics/query.md>), [JOIN](<https://devfeed.tech/topics/join.md>)

Tags: [blog](<https://devfeed.tech/tags/blog.md>), [execution](<https://devfeed.tech/tags/execution.md>), [indexes](<https://devfeed.tech/tags/indexes.md>), [join](<https://devfeed.tech/tags/join.md>), [mysql](<https://devfeed.tech/tags/mysql.md>), [query](<https://devfeed.tech/tags/query.md>), [sql](<https://devfeed.tech/tags/sql.md>), [tips](<https://devfeed.tech/tags/tips.md>)

## AI overview

An example compares two MySQL queries over large post and thread tables. Rewriting a time-based condition so the indexed dateline column is directly compared reduced the reported execution time from about 10 seconds to 0.03 seconds. The article also points to EXPLAIN for examining the execution plan.

## Source excerpt

This might seem elementary to those of you that are DBAs or something similar, but it was fascinating to find out (not to mention it greatly helped what I had to do), so I decided to post it, in case it helps someone else too. A few moments ago I found out that whereas a query along the lines of... SELECT title, COUNT(1) as replies FROM post INNER JOIN thread USING(threadid) WHERE **UNIX\_TIMESTAMP(NOW()) - post.dateline < 86400** GROUP BY threadid ORDER BY replies DESC LIMIT 5 took a whopping ~10 seconds on a post table of around 2,000,000 rows and a thread table of around 40,000 rows, the following: SELECT title, COUNT(1) as replies FROM post INNER JOIN thread USING(threadid) WHERE **post.dateline > UNIX\_TIMESTAMP(NOW()) - 86400** GROUP BY threadid ORDER BY replies DESC LIMIT 5 took a mere 0.03 seconds! Probably, MySQL wasn't able to utilize the B+ tree index of the dateline column in the first query, whereas in the second, things were a bit more obvious to it. This can also be observed by examining the information about the execution plan that EXPLAIN provides: mysql> explain select t.threadid, t.title, count(1) as replies from vb3\_post as p inner join vb3\_thread as t using(threadid) where unix\_timestamp(now()) - p.dateline < 86400 group by p.threadid order by replies desc limit 5; +----+-------------+-------+------+---------------+----------+---------+------------+-------+---------------------------------+ | id | select\_type | table | type | possible\_keys | key | key\_len | re | rows | Extra | +----+-------------+-------+------+---------------+----------+---------+------------+-------+---------------------------------+ | 1 | SIMPLE | t | ALL | PRIMARY | NULL | NULL | NULL | 39859 | Using temporary; Using filesort | | 1 | SIMPLE | p | ref | threadid | threadid | 4 | t.threadid | 49 | Using where | +----+-------------+-------+------+---------------+----------+---------+------------+-------+---------------------------------+ 2 rows in set (0.01 sec) ``` ``` mys