mysql - How to optimise a query containing joins and subqueries -
i have inherited next query , db construction , want optimise slow. contains joins , subqueries i've read isn't plan. i've tried various ways improve getting stuck/lost.
if fine if there suggestions improving immensely grateful...
the query draws info various tables produce study on how many clickthroughs supplier's website, telephone number 'reveals' there have been supplier , emails have been sent supplier.
the clause uses 1=1 conditions added filter study downwards region, county, , supplier's business type.
the code copied mysql_slow log interpolate $variables. construction of tables output mysql dump.
the query:
select business.*, ( select count(message.id) messages message (u.id = message.from_to or u.id = message.user_id) , message.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59' ) message_no, ( select count(distinct(messageunique.user_id)) messages messageunique (u.id = messageunique.from_to or u.id = messageunique.user_id) , (messageunique.parent_message_id null or messageunique.parent_message_id = messageunique.id) , messageunique.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59' ) message_unique_no, ( select count(*) business_counties bc2 business.id = bc2.business_id ) county_no, ( select count(click.id) business_clickthroughs click business.id = click.business_id , click.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59' ) clicks, ( select count(*) business_regions br2 business.id = br2.business_id ) region_no, ( select count(businessreveal.id) reveal_no business_reveals businessreveal 1=1 , businessreveal.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59' , businessreveal.business_id = business.id ) reveals_no businesses business left bring together users u on business.id = u.business_id left bring together business_counties bc on business.id = bc.business_id left bring together businesses_business_types bt on business.id = bt.business_id left bring together business_regions br on business.id = br.business_id 1=1 grouping business.id; the table structures:
/* navicat mysql info transfer source server : _localhost source server type : mysql source server version : 50530 target server type : mysql target server version : 50530 file encoding : utf-8 */ -- ---------------------------- -- table construction `business_clickthroughs` -- ---------------------------- drop table if exists `business_clickthroughs`; create table `business_clickthroughs` ( `id` bigint(12) unsigned not null auto_increment, `business_id` int(8) unsigned not null, `registered_user` tinyint(1) unsigned default '0', `created` datetime not null, primary key (`id`), key `bid` (`business_id`) ) engine=innodb auto_increment=29357 default charset=utf8 row_format=compact; -- ---------------------------- -- table construction `business_counties` -- ---------------------------- drop table if exists `business_counties`; create table `business_counties` ( `id` int(11) not null auto_increment, `business_id` int(11) not null, `county_id` int(11) not null, primary key (`id`), key `bcid` (`business_id`) ) engine=myisam auto_increment=20124 default charset=utf8 collate=utf8_unicode_ci row_format=fixed; -- ---------------------------- -- table construction `business_regions` -- ---------------------------- drop table if exists `business_regions`; create table `business_regions` ( `id` int(11) not null auto_increment, `business_id` int(11) not null, `region_id` int(11) not null, primary key (`id`) ) engine=myisam auto_increment=2719 default charset=utf8 collate=utf8_unicode_ci row_format=fixed; -- ---------------------------- -- table construction `business_reveals` -- ---------------------------- drop table if exists `business_reveals`; create table `business_reveals` ( `id` int(11) not null auto_increment, `business_id` int(11) not null, `customer_id` int(11) default null, `created` datetime not null, `modified` datetime not null, primary key (`id`), key `bid` (`business_id`) ) engine=innodb auto_increment=3172 default charset=latin1 row_format=compact; -- ---------------------------- -- table construction `businesses_business_types` -- ---------------------------- drop table if exists `businesses_business_types`; create table `businesses_business_types` ( `id` int(11) not null auto_increment, `business_id` int(11) not null, `business_type_id` int(11) not null, `level` int(2) not null default '2', primary key (`id`), key `bid` (`business_id`) comment '(null)' ) engine=myisam auto_increment=4484 default charset=utf8 collate=utf8_unicode_ci row_format=fixed; -- ---------------------------- -- table construction `messages` -- ---------------------------- drop table if exists `messages`; create table `messages` ( `id` int(11) not null auto_increment, `subject` varchar(500) default null, `message` text, `user_id` int(11) default null, `message_folder_id` int(11) default null, `parent_message_id` int(11) default null, `status` int(11) default null, `direction` int(11) default null, `from_to` varchar(500) default null, `attachment` varchar(500) default null, `created` datetime default null, `modified` datetime default null, `guest_sender` varchar(255) default null, primary key (`id`), key `fromto` (`from_to`(255)), key `uid` (`user_id`), key `pmid` (`parent_message_id`) ) engine=innodb auto_increment=4582 default charset=utf8 row_format=compact; -- ---------------------------- -- table construction `users` -- ---------------------------- drop table if exists `users`; create table `users` ( `id` int(11) not null auto_increment, `login` varchar(255) collate latin1_general_ci not null, `password` varchar(255) collate latin1_general_ci not null, `name` varchar(255) collate latin1_general_ci not null, `email` varchar(255) collate latin1_general_ci not null, `title` varchar(20) collate latin1_general_ci not null, `firstname` varchar(255) collate latin1_general_ci not null, `lastname` varchar(255) collate latin1_general_ci not null, `active` tinyint(1) not null default '0', `first_visit` tinyint(1) not null default '1', `signature` text collate latin1_general_ci, `type` varchar(45) collate latin1_general_ci default 'customer', `business_id` int(11) default null, `admin_monitor` tinyint(1) not null default '0', `partner_name` varchar(255) collate latin1_general_ci default null, `postcode` varchar(255) collate latin1_general_ci default null, `venue_postcode` varchar(255) collate latin1_general_ci default null, `wedding_date` datetime default null, `phone` varchar(255) collate latin1_general_ci not null, `register_date` datetime default null, `event` text collate latin1_general_ci, `mailing_list` tinyint(1) not null default '0', `created` datetime not null, `modified` datetime not null, primary key (`id`) ) engine=myisam auto_increment=2854 default charset=latin1 collate=latin1_general_ci row_format=dynamic; the explain plan.
id select_type table type possible_keys key key_len ref rows 1 primary business - - - - 444 using temporary; using filesort 1 primary u - - - - 2658 - 1 primary bc ref bcid bcid 4 business.id 7 using index 1 primary bt ref bid bid 4 business.id 9 using index 1 primary br - - - - 440 - 7 dependent subquery businessreveal ref bid bid 4 func 5 using 6 dependent subquery br2 - - - - 440 using 5 dependent subquery click ref bid bid 4 func 22 using 4 dependent subquery bc2 ref bcid bcid 4 func 7 using index 3 dependent subquery messageunique fromto,uid,pmid - - - 4958 using 2 dependent subquery message fromto,uid - - - 4958 using
your query has 6 correlated sub queries, , in total returning 444 rows. each of correlated sub queries beingness executed each returned row. hence single query resulting in under 3000 queries.
personally prefer avoid then, using big bring together or joining against sub queries. depends on number of rows returned
further joining straight tables doing left joins on anyway, generate lot of duplicates grouping excludes. take nil straight of tables , grouping on appears unique key seems irrelevant.
if maintain correlated sub queries:-
select count(message.id) messages message (u.id = message.from_to or u.id = message.user_id) , message.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59' there no useful index on table sub query. checking 2 different columns u.id there not much can done there, index on created help. might improve duplicate sub query, 1 time checking from_to , 1 time checking user_id, , adding results together. have index on relevant id field , date.
also, doing count on value appears unique key should never null.
select count(distinct(messageunique.user_id)) messages messageunique (u.id = messageunique.from_to or u.id = messageunique.user_id) , (messageunique.parent_message_id null or messageunique.parent_message_id = messageunique.id) , messageunique.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59' same problem previous sub query.
select count(*) business_counties bc2 business.id = bc2.business_id this has key on business_id , should ok
select count(click.id) business_clickthroughs click business.id = click.business_id , click.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59' while indexed on business id there no index covers both business id , created date, help here.
select count(*) business_regions br2 business.id = br2.business_id this requires index on business_id on business regions table
select count(businessreveal.id) reveal_no business_reveals businessreveal 1=1 , businessreveal.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59' , businessreveal.business_id = business.id here key does't cover created date, business id.
if want seek doing joins against sub queries (which can more efficient, despite mysql beingness poor @ joining onto sub queries) (not tested):-
select business.*, mess_1.mess_count + mess_2.mess_count message_no, mess_3.mess_count + mess_4.mess_count message_unique_no, business1.county_no, click1.clicks, business_regions.region_no, business_reveals1.reveals_no businesses business left bring together users u on business.id = u.business_id left outer bring together ( select message.from_to, count(message.id) mess_count messages message message.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59' grouping message.from_to ) mess_1 on u.id = mess_1.from_to left outer bring together ( select message.user_id, count(message.id) mess_count messages message message.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59' grouping message.user_id ) mess_2 on u.id = mess_2.user_id left outer bring together ( select messageunique.from_to, count(distinct(messageunique.user_id)) mess_count messages messageunique (messageunique.parent_message_id null or messageunique.parent_message_id = messageunique.id) , messageunique.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59' grouping messageunique.from_to ) mess_3 on u.id = mess_3.from_to left outer bring together ( select messageunique.user_id, count(distinct(messageunique.user_id)) mess_count messages messageunique (messageunique.parent_message_id null or messageunique.parent_message_id = messageunique.id) , messageunique.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59' grouping messageunique.user_id ) mess_4 on u.id = mess_4.from_to left outer bring together ( select business_id, count(*) county_no business_counties bc2 grouping business.id ) business1 on business.id = business1.business_id left outer bring together ( select click.business_id, count(click.id) clicks business_clickthroughs click click.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59' grouping click.business_id ) click1 on business.id = click1.business_id left outer bring together ( select br2.business_id, count(*) region_no business_regions br2 business.id = br2.business_id grouping br2.business_id ) business_regions on business.id = business_regions.business_id left outer bring together ( select businessreveal.business_id, count(businessreveal.id) reveal_no business_reveals businessreveal businessreveal.created between '2014-04-01 00:00:00' , '2014-04-30 23:59:59' grouping businessreveal.business_id ) business_reveals1 on business_reveals1.business_id = business.id mysql sql join optimization subquery
No comments:
Post a Comment