mysql - Explain select output on a complex query that I didn't write -
i'm working on codebase didn't write , we're getting bad performance on our sql queries, thought we're missing indices, having badly-written queries, etc. using mysql. can see way query name of genres instead of kind of genre id db mess. trying explain query figure out why query biggest offender in terms of run time.
explain select count(v.id) cnt non_detail_video_views vv join video v on vv.video_id = v.id join video_sub_category c on v.video_sub_category_id = c.id date(from_unixtime(vv.created_date)) = '2014-06-25' , c.sub_category_name in ('rap','r&b','country','pop','metal','rock' ,'edm','christian','alternative','jazz','blues' ,'reggae','classical','folk','trance') group vv.video_id order vv.created_date desc; i asked guy direct access db run query , gave me screenshot back:
this non_detail_video_views table:
create table if not exists `non_detail_video_views` ( `id` int(11) not null auto_increment, `video_id` int(11) not null, `sharer_id` int(11) not null, `member_id` int(11) not null, `is_newgame` int(11) not null, `frompage` int(11) not null, `from_country` varchar(255) not null, `ip_address` varchar(255) not null, `created_date` int(11) not null, `updated_date` int(11) not null, `active` int(11) not null, primary key (`id`), key `id` (`id`), key `video_id` (`video_id`), key `created_date` (`created_date`) ) engine=innodb default charset=latin1; video table:
create table if not exists `video` ( `id` int(11) not null auto_increment, `referral_id` int(11) not null, `user_id` int(11) not null, `video_title` varchar(255) not null, `video_description` varchar(2048) default null, `video_url` varchar(1024) not null, `video_html` varchar(2048) not null, `video_category_id` int(11) not null, `video_sub_category_id` int(11) not null, `video_thump` varchar(255) not null, `price` decimal(10,2) not null, `payment_type` int(11) not null, `in_contest` int(11) not null default '0', `repeater` int(11) not null default '1', `coupon_code` varchar(255) not null, `created_date` int(11) not null, `updated_date` int(11) not null, `active` int(11) not null, `noofshares` varchar(255) not null, `besides` int(11) not null, `facebookpage` varchar(255) not null, `custom_message` varchar(255) not null, `custom_link` varchar(255) not null, `video_country_id` varchar(255) not null, `ages` varchar(255) not null, `gender` int(11) not null, `duration` int(11) not null, `likes` int(11) not null, `views` int(11) not null, `comments` varchar(255) not null, `subscribers` varchar(255) not null, `pay_per_view` double(10,2) not null, `total_payment` double(10,2) not null, `pending_amount` double(10,2) not null, `second_payment` int(11) not null, `welcome_mail` int(11) not null, `add_budget_mail` int(11) not null, `auto_mid_way` int(11) not null, `out_of_budget` int(11) not null, `out_of_budget_2` int(11) not null, `out_of_budget_3` int(11) not null, `is_refunded` int(11) not null, `refund_date` int(11) not null, `is_banned` int(11) not null, primary key (`id`), key `id` (`id`), key `video_id` (`video_url`(767)), key `created_date` (`created_date`) ) engine=innodb default charset=latin1; video_sub_category table:
create table if not exists `video_sub_category` ( `id` int(11) not null auto_increment, `category_id` int(11) not null, `sub_category_name` varchar(255) not null, `genre_id` int(11) not null, `created_date` int(11) not null, `updated_date` int(11) not null, `active` int(11) not null default '1', primary key (`id`) ) engine=innodb default charset=latin1; i haven't done before, how interpret output?
the thing can suggest follows. rather doing steps , trying query, suggest try first step, query, second step, query, etc. not scientific know, that's how roll...
change date comparison
where vv.created_date between 1403650800 , 1403737199remove existing indexes on vv.video_id , vv.created_date , instead create compound index on these columns.
add index on v.video_sub_category_id
add index on c.sub_category_name
Comments
Post a Comment