Closed butonic closed 2 years ago
mysql> SHOW INDEXES FROM oc_oauth2_access_tokens; +-------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ | oc_oauth2_access_tokens | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | YES | NULL | +-------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+ 1 row in set (0.00 sec)
so the SELECT where token query has to do a full table scan:
mysql> EXPLAIN SELECT * FROM oc_oauth2_access_tokens WHERE token = 'a very long token ...'; +----+-------------+-------------------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | oc_oauth2_access_tokens | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------------------------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
how to fix?
mysql> CREATE UNIQUE INDEX oauth2_token ON oc_oauth2_access_tokens(token); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM oc_oauth2_access_tokens WHERE token = 'a very long token ...'; +----+-------------+-------------------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------------------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | oc_oauth2_access_tokens | NULL | ref | oauth2_token | oauth2_token | 194 | const | 1 | 100.00 | NULL | +----+-------------+-------------------------+------------+------+---------------+--------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
so the SELECT where token query has to do a full table scan:
how to fix?