我有一个表,其中包含用逗号分隔的URL列表。忽略应该更新架构的事实。以下两个语句均起作用:

SELECT id FROM website WHERE url LIKE '%example.com%';

SELECT id FROM website WHERE FIND_IN_SET('example.com', url);


有没有一种很好的方法来衡量查询的性能?有没有更好的方法(不更新架构)呢?

使用EXPLAIN,我得到以下结果:

+-------------+--------+---------+------+----------+
| select_type | type   | key     | rows | filtered |
+-------------+--------+---------+------+----------+
| SIMPLE      | ALL    | NULL    |  5   |   20.00  | (LIKE)
| SIMPLE      | ALL    | NULL    |  5   |   100.00 | (FIND_IN_SET)
+-------------+-------+----------+------+----------+

最佳答案

在MariaDB中,您可以在查询后用SET profiling=ON;SHOW PROFILE;看到它。

样品

MariaDB [test]>  SET profiling=ON;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]>  SELECT FIND_IN_SET('2', '1,2,3,4,5');
+-------------------------------+
| FIND_IN_SET('2', '1,2,3,4,5') |
+-------------------------------+
|                             2 |
+-------------------------------+
1 row in set (0.000 sec)

MariaDB [test]>  SHOW PROFILE;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| Starting                       | 0.000025 |
| Waiting for query cache lock   | 0.000005 |
| Init                           | 0.000004 |
| Checking query cache for query | 0.000047 |
| Checking permissions           | 0.000007 |
| Opening tables                 | 0.000011 |
| After opening tables           | 0.000007 |
| Init                           | 0.000014 |
| Optimizing                     | 0.000012 |
| Executing                      | 0.000010 |
| End of update loop             | 0.000005 |
| Query end                      | 0.000003 |
| Commit                         | 0.000004 |
| Closing tables                 | 0.000003 |
| Starting cleanup               | 0.000004 |
| Freeing items                  | 0.000006 |
| Updating status                | 0.000014 |
| Reset for next command         | 0.000004 |
+--------------------------------+----------+
18 rows in set (0.000 sec)

MariaDB [test]>

关于mysql - MySQL列搜索逗号分隔的项目-FIND_IN_SET或LIKE,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/53723054/

10-10 22:05