API tools faq
paste
A248

Forked query increases index usage in MariaDB

A248
Apr 7th, 2026
82
0
Never
9
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 12.81 KB | None | 0 0
raw download clone embed print report
  1. MariaDB [bans]> explain select `libertybans_applicable_bans`.`victim_type`,
  2.     -> `libertybans_applicable_bans`.`victim_uuid`, `libertybans_applicable_bans`.`victim_address`,
  3.     -> `libertybans_applicable_bans`.`operator`, `libertybans_applicable_bans`.`reason`,
  4.     -> `libertybans_applicable_bans`.`scope_type`, `libertybans_applicable_bans`.`scope`, `libertybans_applicable_bans`.`start`,
  5.     -> `libertybans_applicable_bans`.`end`, `libertybans_applicable_bans`.`track`, `libertybans_applicable_bans`.`id`
  6.     -> from `libertybans_applicable_bans`
  7.     -> inner join `libertybans_strict_links` on `libertybans_applicable_bans`.`uuid` = `libertybans_strict_links`.`uuid1`
  8.     -> where ((`libertybans_applicable_bans`.`end` = 0 or `libertybans_applicable_bans`.`end` > 1627005548)
  9.     -> and
  10.     -> (`libertybans_strict_links`.`uuid1` = X'9edbfbc5802b41028d5e3ee25d163913' or (`libertybans_strict_links`.`uuid2` = X'9edbfbc5802b41028d5e3ee25d163913'
  11.     -> and `libertybans_applicable_bans`.`victim_type` <> 0)))
  12.     -> order by case `libertybans_applicable_bans`.`end`
  13.     -> when 0 then 9223372036854775807 else `libertybans_applicable_bans`.`end` end desc limit 1;
  14. +------+-------------+---------+--------+-----------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------+------+------------------------------------------------+
  15. | id   | select_type | table   | type   | possible_keys                                                                           | key                                  | key_len | ref                 | rows | Extra                                          |
  16. +------+-------------+---------+--------+-----------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------+------+------------------------------------------------+
  17. |    1 | SIMPLE      | thetype | index  | libertybans_ban_id_uniqueness,libertybans_ban_victim_uniqueness                         | libertybans_ban_victim_uniqueness    | 4       | NULL                | 17   | Using index; Using temporary; Using filesort   |
  18. |    1 | SIMPLE      | puns    | eq_ref | libertybans_punishment_id_uniqueness,libertybans_punishment_end_index                   | libertybans_punishment_id_uniqueness | 8       | bans.thetype.id     | 1    | Using where                                    |
  19. |    1 | SIMPLE      | tracks  | eq_ref | libertybans_track_id_uniqueness                                                         | libertybans_track_id_uniqueness      | 4       | bans.puns.track     | 1    | Using where                                    |
  20. |    1 | SIMPLE      | victims | eq_ref | libertybans_victim_id_uniqueness,libertybans_victim_uniqueness,uuid_index,address_index | libertybans_victim_id_uniqueness     | 4       | bans.thetype.victim | 1    | Using where                                    |
  21. |    1 | SIMPLE      | scopes  | eq_ref | libertybans_scope_id_uniqueness                                                         | libertybans_scope_id_uniqueness      | 4       | bans.puns.scope_id  | 1    | Using where                                    |
  22. |    1 | SIMPLE      | addrs   | ALL    | libertybans_uuid_address_uniqueness,libertybans_address_index                           | NULL                                 | NULL    | NULL                | 8960 | Range checked for each record (index map: 0x3) |
  23. |    1 | SIMPLE      | addrs1  | ref    | libertybans_uuid_address_uniqueness,libertybans_address_index                           | libertybans_uuid_address_uniqueness  | 16      | bans.addrs.uuid     | 1    |                                                |
  24. |    1 | SIMPLE      | addrs2  | ref    | libertybans_uuid_address_uniqueness,libertybans_address_index                           | libertybans_address_index            | 18      | bans.addrs1.address | 2    | Using where; Using index                       |
  25. +------+-------------+---------+--------+-----------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------+------+------------------------------------------------+
  26. 8 rows in set (0.004 sec)
  27.  
  28. MariaDB [bans]> explain select `libertybans_applicable_bans`.`victim_type`,
  29.     -> `libertybans_applicable_bans`.`victim_uuid`, `libertybans_applicable_bans`.`victim_address`,
  30.     -> `libertybans_applicable_bans`.`operator`, `libertybans_applicable_bans`.`reason`,
  31.     -> `libertybans_applicable_bans`.`scope_type`, `libertybans_applicable_bans`.`scope`, `libertybans_applicable_bans`.`start`,
  32.     -> `libertybans_applicable_bans`.`end`, `libertybans_applicable_bans`.`track`, `libertybans_applicable_bans`.`id`
  33.     -> from `libertybans_applicable_bans`
  34.     -> inner join `libertybans_strict_links` on `libertybans_applicable_bans`.`uuid` = `libertybans_strict_links`.`uuid1`
  35.     -> where ((`libertybans_applicable_bans`.`end` = 0 or `libertybans_applicable_bans`.`end` > 1627005548)
  36.     -> and
  37.     -> (`libertybans_strict_links`.`uuid1` = X'9edbfbc5802b41028d5e3ee25d163913'))
  38.     -> order by case `libertybans_applicable_bans`.`end`
  39.     -> when 0 then 9223372036854775807 else `libertybans_applicable_bans`.`end` end desc limit 1;
  40. +------+-------------+---------+--------+-----------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------+------+--------------------------------------------------------------+
  41. | id   | select_type | table   | type   | possible_keys                                                                           | key                                  | key_len | ref                 | rows | Extra                                                        |
  42. +------+-------------+---------+--------+-----------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------+------+--------------------------------------------------------------+
  43. |    1 | SIMPLE      | addrs   | ref    | libertybans_uuid_address_uniqueness,libertybans_address_index                           | libertybans_uuid_address_uniqueness  | 16      | const               | 1    | Using where; Using temporary; Using filesort                 |
  44. |    1 | SIMPLE      | addrs1  | ref    | libertybans_uuid_address_uniqueness,libertybans_address_index                           | libertybans_uuid_address_uniqueness  | 16      | const               | 1    | Using where                                                  |
  45. |    1 | SIMPLE      | addrs2  | ref    | libertybans_address_index                                                               | libertybans_address_index            | 18      | bans.addrs1.address | 2    | Using index                                                  |
  46. |    1 | SIMPLE      | victims | range  | libertybans_victim_id_uniqueness,libertybans_victim_uniqueness,uuid_index,address_index | libertybans_victim_uniqueness        | 18      | NULL                | 5    | Using where; Using index; Using join buffer (flat, BNL join) |
  47. |    1 | SIMPLE      | thetype | eq_ref | libertybans_ban_id_uniqueness,libertybans_ban_victim_uniqueness                         | libertybans_ban_victim_uniqueness    | 4       | bans.victims.id     | 1    | Using index                                                  |
  48. |    1 | SIMPLE      | puns    | eq_ref | libertybans_punishment_id_uniqueness,libertybans_punishment_end_index                   | libertybans_punishment_id_uniqueness | 8       | bans.thetype.id     | 1    | Using where                                                  |
  49. |    1 | SIMPLE      | tracks  | eq_ref | libertybans_track_id_uniqueness                                                         | libertybans_track_id_uniqueness      | 4       | bans.puns.track     | 1    | Using where                                                  |
  50. |    1 | SIMPLE      | scopes  | eq_ref | libertybans_scope_id_uniqueness                                                         | libertybans_scope_id_uniqueness      | 4       | bans.puns.scope_id  | 1    | Using where                                                  |
  51. +------+-------------+---------+--------+-----------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------+------+--------------------------------------------------------------+
  52. 8 rows in set (0.004 sec)
  53.  
  54. MariaDB [bans]> explain select `libertybans_applicable_bans`.`victim_type`,
  55.     -> `libertybans_applicable_bans`.`victim_uuid`, `libertybans_applicable_bans`.`victim_address`,
  56.     -> `libertybans_applicable_bans`.`operator`, `libertybans_applicable_bans`.`reason`,
  57.     -> `libertybans_applicable_bans`.`scope_type`, `libertybans_applicable_bans`.`scope`, `libertybans_applicable_bans`.`start`,
  58.     -> `libertybans_applicable_bans`.`end`, `libertybans_applicable_bans`.`track`, `libertybans_applicable_bans`.`id`
  59.     -> from `libertybans_applicable_bans`
  60.     -> inner join `libertybans_strict_links` on `libertybans_applicable_bans`.`uuid` = `libertybans_strict_links`.`uuid1`
  61.     -> where ((`libertybans_applicable_bans`.`end` = 0 or `libertybans_applicable_bans`.`end` > 1627005548)
  62.     -> and
  63.     -> ((`libertybans_strict_links`.`uuid2` = X'9edbfbc5802b41028d5e3ee25d163913'
  64.     -> and `libertybans_applicable_bans`.`victim_type` <> 0)))
  65.     -> order by case `libertybans_applicable_bans`.`end`
  66.     -> when 0 then 9223372036854775807 else `libertybans_applicable_bans`.`end` end desc limit 1;
  67. +------+-------------+---------+--------+-----------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------+------+--------------------------------------------------------------+
  68. | id   | select_type | table   | type   | possible_keys                                                                           | key                                  | key_len | ref                 | rows | Extra                                                        |
  69. +------+-------------+---------+--------+-----------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------+------+--------------------------------------------------------------+
  70. |    1 | SIMPLE      | addrs2  | ref    | libertybans_uuid_address_uniqueness,libertybans_address_index                           | libertybans_uuid_address_uniqueness  | 16      | const               | 1    | Using where; Using temporary; Using filesort                 |
  71. |    1 | SIMPLE      | addrs1  | ref    | libertybans_uuid_address_uniqueness,libertybans_address_index                           | libertybans_address_index            | 18      | bans.addrs2.address | 2    | Using index                                                  |
  72. |    1 | SIMPLE      | addrs   | ref    | libertybans_uuid_address_uniqueness,libertybans_address_index                           | libertybans_uuid_address_uniqueness  | 16      | bans.addrs1.uuid    | 1    |                                                              |
  73. |    1 | SIMPLE      | victims | range  | libertybans_victim_id_uniqueness,libertybans_victim_uniqueness,uuid_index,address_index | libertybans_victim_uniqueness        | 2       | NULL                | 4    | Using where; Using index; Using join buffer (flat, BNL join) |
  74. |    1 | SIMPLE      | thetype | eq_ref | libertybans_ban_id_uniqueness,libertybans_ban_victim_uniqueness                         | libertybans_ban_victim_uniqueness    | 4       | bans.victims.id     | 1    | Using index                                                  |
  75. |    1 | SIMPLE      | puns    | eq_ref | libertybans_punishment_id_uniqueness,libertybans_punishment_end_index                   | libertybans_punishment_id_uniqueness | 8       | bans.thetype.id     | 1    | Using where                                                  |
  76. |    1 | SIMPLE      | tracks  | eq_ref | libertybans_track_id_uniqueness                                                         | libertybans_track_id_uniqueness      | 4       | bans.puns.track     | 1    | Using where                                                  |
  77. |    1 | SIMPLE      | scopes  | eq_ref | libertybans_scope_id_uniqueness                                                         | libertybans_scope_id_uniqueness      | 4       | bans.puns.scope_id  | 1    | Using where                                                  |
  78. +------+-------------+---------+--------+-----------------------------------------------------------------------------------------+--------------------------------------+---------+---------------------+------+--------------------------------------------------------------+
  79. 8 rows in set (0.003 sec)
Advertisement
Comments
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
  • User was banned
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!