mysql group by using temporary_MySQL GROUP BY with Using Temporary unnecessarily?

I am trying to optimize a query. Using EXPLAIN tells me it is Using temporary. This is really inefficient given the size of the table (20m+ records). Looking at the MySQL documentation Internal Temporary Tables I don't see anything that would imply the need for a Temporary table in my query. I also tried setting the ORDER BY to the same as the GROUP BY, but still says Using Temporary and query takes forever to run. I am using MySQL 5.7.

Is there a way to avoid using a temporary table for this query:

SELECT url,count(*) as sum

FROM `digital_pageviews` as `dp`

WHERE `publisher_uuid` = '8b83120e-3e19-4c34-8556-7b710bd7b812'

GROUP BY url

ORDER BY NULL;

This is my table schema:

create table digital_pageviews

(

id int unsigned auto_increment

primary key,

visitor_uuid char(36) null,

publisher_uuid char(36) default '' not null,

property_uuid char(36) null,

ip_address char(15) not null,

referrer text null,

url_delete text null,

url varchar(255) null,

url_tmp varchar(255) null,

meta text null,

date_created timestamp not null,

date_updated timestamp null

)

collate = utf8_unicode_ci;

create index digital_pageviews_url_index

on digital_pageviews (url);

create index ndx_date_created

on digital_pageviews (date_created);

create index ndx_property_uuid

on digital_pageviews (property_uuid);

create index ndx_publisher_uuid

on digital_pageviews (publisher_uuid);

create index ndx_visitor_uuid_page

on digital_pageviews (visitor_uuid);

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值