gpt4 book ai didi

mysql - 如何加速这个 MySQL 查询?

转载 作者:行者123 更新时间:2023-11-29 06:58:31 27 4
gpt4 key购买 nike

mysql> EXPLAIN SELECT col1, col2 FROM t WHERE col1 REGEXP '^foobar_[0-9]{4}$';
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | t | ALL | NULL | NULL | NULL | NULL | 757000 | Using where |
+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.15 sec)

mysql> DESCRIBE t;
+------------------------+------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+------------------------+------+-----+---------+-------+
| col2 | varchar(255) | NO | MUL | {} | |
| col1 | varbinary(255) | NO | PRI | | |
+------------------------+------------------------+------+-----+---------+-------+
2 rows in set (0.22 sec)

最佳答案

如果你在 col1 上有一个索引,然后像这样添加一个 LIKE:

SELECT col1, col2
FROM t
WHERE col1 LIKE 'foobar_%' AND col1 REGEXP '^foobar_[0-9]{4}$';

你应该获得更快的速度。

你也可以为 (col1, col2) 建立一个索引,让 mysql 在内存中存储该表的副本

关于mysql - 如何加速这个 MySQL 查询?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/11338285/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com