Slow Query: admin query by IP doesn't use an index

[dfabulich]

# Time: 231005 19:18:24
# User@Host: ifdb[ifdb] @ localhost [127.0.0.1]
# Thread_id: 4126162  Schema: ifdb  QC_hit: No
# Query_time: 13.619014  Lock_time: 0.000118  Rows_sent: 5  Rows_examined: 135166
# Rows_affected: 0  Bytes_sent: 700
# Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 202520
# Full_scan: Yes  Full_join: Yes  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
#
# explain: id   select_type table   type    possible_keys   key key_len ref rows    r_rows  filtered    r_filtered  Extra
# explain: 1    SIMPLE  a   ALL NULL    NULL    NULL    NULL    67871   67871.00    100.00  100.00  Using temporary; Using filesort
# explain: 1    SIMPLE  u   eq_ref  PRIMARY PRIMARY 34  ifdb.a.uid  1   0.99    100.00  100.00  
# explain: 1    SIMPLE  b   ALL NULL    NULL    NULL    NULL    67871   67871.00    100.00  0.00    Using where; Using join buffer (flat, BNL join)
#
SET timestamp=1696533504;
select u.id, u.name, u.email, b.ip, date_format(b.`when`, '%M %e, %Y')
        from logins as a
          join logins as b on a.ip = b.ip and a.uid != b.uid
          join users as u on u.id = a.uid
        where b.uid = 'nufzrftl37o9rw5t'
        group by u.id
        order by b.`when` desc;

MariaDB [ifdb]> describe logins;
+-------+-------------+------+-----+---------------------+-------+
| Field | Type        | Null | Key | Default             | Extra |
+-------+-------------+------+-----+---------------------+-------+
| uid   | varchar(32) | NO   |     |                     |       |
| ip    | varchar(16) | NO   |     |                     |       |
| when  | datetime    | NO   |     | 0000-00-00 00:00:00 |       |
+-------+-------------+------+-----+---------------------+-------+
3 rows in set (0.001 sec)

MariaDB [ifdb]> select count(*) from logins;
+----------+
| count(*) |
+----------+
|    67982 |
+----------+
1 row in set (0.000 sec)

[salty-horse]

This is just a matter of adding an index to the IP column, no?

BTW, IPv4's can be stored more efficiently as unsigned ints.

[dfabulich]

It's still not using the index!

# Time: 240104 19:11:52
# User@Host: ifdb[ifdb] @ localhost [127.0.0.1]
# Thread_id: 75916476  Schema: ifdb  QC_hit: No
# Query_time: 30.100709  Lock_time: 0.000175  Rows_sent: 5  Rows_examined: 2055991
# Rows_affected: 0  Bytes_sent: 700
# Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 202520
# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
#
# explain: id   select_type table   type    possible_keys   key key_len ref rows    r_rows  filtered    r_filtered  Extra
# explain: 1    SIMPLE  a   ALL ip  NULL    NULL    NULL    68945   68945.00    100.00  100.00  Using temporary; Using filesort
# explain: 1    SIMPLE  u   eq_ref  PRIMARY PRIMARY 34  ifdb.a.uid  1   0.99    100.00  100.00
# explain: 1    SIMPLE  b   ref ip  ip  18  ifdb.a.ip   2   28.07   100.00  0.02    Using where
#
SET timestamp=1704395512;
select u.id, u.name, u.email, b.ip, date_format(b.`when`, '%M %e, %Y')
        from logins as a
          join logins as b on a.ip = b.ip and a.uid != b.uid
          join users as u on u.id = a.uid
        where b.uid = 'nufzrftl37o9rw5t'
        group by u.id
        order by b.`when` desc;

[dfabulich]

Added an index on uid, too. Now it's working.

MariaDB [ifdb]> show index from logins;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| logins |          1 | ip       |            1 | ip          | A         |       34472 |     NULL | NULL   |      | BTREE      |         |               |
| logins |          1 | uid      |            1 | uid         | A         |       17236 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.001 sec)

MariaDB [ifdb]> analyze select u.id, u.name, u.email, b.ip, date_format(b.`when`, '%M %e, %Y')
    ->         from logins as a
    ->           join logins as b on a.ip = b.ip and a.uid != b.uid
    ->           join users as u on u.id = a.uid
    ->         where b.uid = 'nufzrftl37o9rw5t'
    ->         group by u.id
    ->         order by b.`when` desc;
+------+-------------+-------+--------+---------------+---------+---------+------------+------+--------+----------+------------+---------------------------------------------------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | r_rows | filtered | r_filtered | Extra                                                               |
+------+-------------+-------+--------+---------------+---------+---------+------------+------+--------+----------+------------+---------------------------------------------------------------------+
|    1 | SIMPLE      | b     | ref    | ip,uid        | uid     | 34      | const      | 297  | 681.00 |   100.00 |     100.00 | Using index condition; Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | a     | ref    | ip,uid        | ip      | 18      | ifdb.b.ip  | 2    | 73.22  |    99.57 |       0.90 | Using where                                                         |
|    1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY | 34      | ifdb.a.uid | 1    | 0.76   |   100.00 |     100.00 |                                                                     |
+------+-------------+-------+--------+---------------+---------+---------+------------+------+--------+----------+------------+---------------------------------------------------------------------+
3 rows in set (0.108 sec)