How to use SQL indexes?

Question

I have an app with n different pages, Now I want to keep track of page visit every time a user visits my page. So, I have created a table where I'm storing log details of a user as below:

| id | visited_page_name |last_visited_at             |user_id  |
+----+----------------------------+----------------------------+-
|  1 |                 1 | 2020-12-25 21:06:38.694452 |  459050 |
|  2 |                 1 | 2020-12-25 21:07:01.072066 |   78557 |
|  3 |                 2 | 2020-12-25 21:07:07.793581 |   78557 |
|  4 |                 3 | 2020-12-25 21:07:16.180584 |   78557 |
|  5 |                 2 | 2020-12-25 21:07:24.339918 |  459050 |
|  6 |                 2 | 2020-12-25 21:07:34.186563 |   78558 |
|  7 |                 3 | 2020-12-25 21:07:40.429625 |   78558 |

And I have made(user, visted_page_name) unique and indexes (last_visited_at, visted_oage_name). See below table for details:

+-------------------------------+------------+-----------------------------------------------------------------+--------------+-------------------+-----------
| Table| Non_unique| Key_name                                   | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type 
--------+-------------+----------+--------+------+-------------------+-------------+----------+--------+------+-------------------+-------------+----------+----
| log |          0 | PRIMARY                                    |            1 | id                | A         |           7 |     NULL | NULL   |      | BTREE
| log |          0 | log_user_id_visited_page_nam_4569a199_uniq |            1 | user_id           | A         |           7 |     NULL | NULL   |      | BTREE 
| log |          0 | log_user_id_visited_page_nam_4569a199_uniq |            2 | visited_page_name | A         |           7 |     NULL | NULL   |      | BTREE  
| log |          1 | log_visited_d9fd74_idx                     |            1 | visited_at        | A         |           7 |     NULL | NULL   |      | BTREE 
| log |          1 | log_visited_d9fd74_idx                     |            2 | visited_page_name | A         |           7 |     NULL | NULL   |      | BTREE  
+-------------------------------+------------+-----------------------------------------------------------------+--------------+-------------------+---

Now I'm using EXPLAIN EXTENDED SELECT * from log WHERE last_visited_at <= 2020-12-25 21:07:40.429625 and visited_page_name in (1, 2)\G. Which gives me below reults:

           id: 1
  select_type: SIMPLE
        table: log
   partitions: NULL
         type: ALL
possible_keys: log_visited_d9fd74_idx
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
     filtered: 14.29
        Extra: Using where

This shows that my query is not using indexing. So, Can anyone please explain why is this happening? What mistakes have I made?

`possible_keys` is not NULL yet the key is not used. There can be [different reasons](https://stackoverflow.com/questions/5719392/why-isnt-mysql-using-any-of-these-possible-keys) for this, from problems with the query to the query optimizer deciding it's actually less efficient to use the index. In this case, I'd guess it's the latter: The DB knows you only have seven rows in this table, so it doesn't bother with the index. — Noah, Dec 25 '20 at 22:15

score 0 · Accepted Answer · answered Dec 25 '20 at 22:54

0

The index log_visited_d9fd74_idx (visited_at, visited_page_name) is a candidate to be used, but as your table only contain 7 rows, it is faster to read the table than go through an index. When your table grows, the query will likely to use the index.

The index log_user_id_visited_page_nam_4569a199_uniq (user_id, visited_page_name) probably should not be unique unless you want trouble when user visits that same page more than once.

answered Dec 25 '20 at 22:54

slaakso

8,331
2
16
27

Yes, I am updating the visited_at when the user visits the same page again. Also, I have a periodic task that deletes entries if they're older than some fixed time. For that reason, I have made `(user_id, visited_page_name)` unique. – Arpit Sachan Dec 26 '20 at 05:48

How to use SQL indexes?

1 Answers1