MySQL查询-优化 [英] MySQL query - optimized

问题描述

是否可以通过一个 MySQL 查询来完成此任务?

Is it possible to achieve this task with a single MySQL query?

表 urls .字段{ id ， url }

1，www.mysite.kom
2，mysite.kom
3，othersite.kom

1, www.mysite.kom
2, mysite.kom
3, anothersite.kom

表日志.字段{ id ， url_id ， group_type -范围为1..10}的数字

Table logs. Fields {id, url_id, group_type - a number in the range of 1..10}

1、1、4
2、1、4
3、2、5
4，2，5
5、3、9

1, 1, 4
2, 1, 4
3, 2, 5
4, 2, 5
5, 3, 9

此示例中的查询结果应为: 1 (mysite.com和www.mysite.com = 1)

The result of the query in this example should be: 1 (mysite.com and www.mysite.com = 1)

目标:

需要计算记录在 logs 表中的所有不同的url，但有一些条件:

Need to count all distinct urls recorded in logs table, but with a few conditions:

1)带有和不带有 www.前缀的网址，例如 mysite.kom 和 www.mysite.kom ，
计为 1 (不是2).

1) Urls with and without www. prefix, like mysite.kom and www.mysite.kom,
should be counted as 1 (not 2).

2)将 group_type 设置为 4..6

3)现在，出现在 group_type 小于4的那些列表中的任何具有 group_type 4..6 的url-应该被忽略，而不是算了.

3) Now, any of these urls with group_type 4..6, which appear in the list of those with group_type lower than 4 - should be ignored and not counted at all.

SQL代码:

SELECT COUNT(DISTINCT TRIM(LEADING 'www.' FROM b.url))
FROM logs a
INNER JOIN urls b
ON a.url_id = b.id
WHERE (group_type BETWEEN 4 AND 6) 

----- and this condition below -----

AND TRIM(LEADING 'www.' FROM b.url)
NOT IN (
  SELECT TRIM(LEADING 'www.' FROM b.url)
  FROM logs a
  INNER JOIN urls b
  ON a.url_id = b.id
  WHERE (group_type < 4)
)

如果我的sql查询正确，可以对其进行优化(看起来更紧凑)吗?

If my sql query is correct, can it be optimized (to look more compact)?

推荐答案

这是一种方法:

SELECT trimmed_url
  FROM ( SELECT TRIM(LEADING 'www.' FROM urls.url) AS trimmed_url,
                MIN(logs.group_type) AS min_group_type
           FROM logs
           JOIN urls
             ON urls.id = logs.url_id
          GROUP
             BY trimmed_url
       ) t
 WHERE min_group_type BETWEEN 4 AND 6
;

但是只有您可以判断它是否看起来更紧凑，只有通过测试才能确定它是否更好.

But only you can judge whether it looks more compact to you, and only testing can determine whether it performs better.

这篇关于MySQL查询-优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！