MySQL distinct+sort query performance issue -

-

problem

the following query takes in excess of 30 seconds run unless:

  • i remove sort (query <1 sec)
  • i remove distinct keyword: (query <1 sec)
  • start removing joins (query <5 secs)

question

how can make query run in under 1 sec. required: how can unique list of meetings related data described joins below including sort of kind.

the related data used both determining if there related field , doing group_concat operations - hence requirement have 3 different joins same bookeditems table.

thanks in advance & or suggestions! i've been banging head on 1 few hours!

query

select  distinct( `meetings`.`id` ) `meeting_id`, `meetings`.`uid` meeting_uid, `meetings_serv`.`id` meetings_serv_id,  `meetings_transp`.`id` meetings_transp_id,  `meetings_acco`.`id` meetings_acco_id,  `meetings_bookeditems`.`id` meetings_bookeditems_id  `meetings` meetings   left outer join `bookeditems` `meetings_serv`  on `meetings`.`uid` = `meetings_serv`.`meeting_uid` , 'ser' = `meetings_serv`.`item_type`   left outer join `bookeditems` `meetings_transp`  on `meetings`.`uid` = `meetings_transp`.`meeting_uid` , 'tra' = `meetings_transp`.`item_type`   left outer join `bookeditems` `meetings_acco`  on `meetings`.`uid` = `meetings_acco`.`meeting_uid` , 'acc' = `meetings_acco`.`item_type`   left outer join `bookeditems` `meetings_bookeditems`  on `meetings`.`uid` = `meetings_bookeditems`.`meeting_uid`   order `meetings`.`datetime`  limit 0, 50

table definition

create table if not exists `bookeditems` (   `id` int(11) not null auto_increment,   `meeting_uid` varchar(256) not null,   `item_type` varchar(256) not null,   primary key (`id`),   key `meeting_uid` (`meeting_uid`(255)),   key `index1` (`meeting_uid`(255),`item_type`(255)) ) engine=innodb  default charset=utf8 auto_increment=5889 ;  create table if not exists `meetings` (   `id` int(11) not null auto_increment,   `uid` varchar(256) not null,   `datetime` datetime default null,   primary key (`id`),   key `uid` (`uid`(255)),   key `datetime` (`datetime`) ) engine=innodb  default charset=utf8 auto_increment=7487 ;

result of explain select

id | select_type | table                | type | possible_keys      | key         | key_len | ref                      | rows | ------------------------------------------------------------------------------------------------------------------------------------- 1  | simple      | meetings             |  | null               | null        | null    | null                     | 7483 | using temporary; using filesort 1  | simple      | meetings_serv        | ref  | meeting_uid,index1 | meeting_uid | 767     | test.meetings.uid        | 1    |  1  | simple      | meetings_transp      | ref  | meeting_uid,index1 | meeting_uid | 767     | test.meetings.uid        | 1    |  1  | simple      | meetings_acco        | ref  | meeting_uid,index1 | meeting_uid | 767     | test.meetings.uid        | 1    |  1  | simple      | meetings_bookeditems | ref  | meeting_uid,index1 | meeting_uid | 767     | test.meetings.uid        | 1    |

result of profiling

starting                      0.000092 checking permissions          0.000003 checking permissions          0.000002 checking permissions          0.000001 checking permissions          0.000001 checking permissions          0.000003 opening tables                0.000036 system lock                   0.000008 init                          0.000033 optimizing                    0.000005 statistics                    0.000035 preparing                     0.000019 creating tmp table            0.000165 executing                     0.000004 copying tmp table          1.790968 converting heap myisam     1.669041 copying tmp table on disk  28.32606 sorting result                0.141737 sending data                  0.000099 end                           0.000005 removing tmp table            0.022097 end                           0.000014 query end                     0.000008 closing tables                0.000017 freeing items                 0.000779 logging slow query            0.000004 cleaning                   0.000005

part solution

based on eric r. rath's below have profiled query , adding max_heap_table_size=256m & tmp_table_size=256m mysql config have been able eliminate execution steps "converting heap myisam" , "copying tmp table on disk".

although beings total execution time down under 2 seconds, still not convinced can if there other suggestions in terms of query optimisation please let me know.

profiling following max_heap_table_size & tmp_table_size config

... executing                     0.000004 copying tmp table          1.790968 sorting result                0.141737 ...

i think index2 unnecessary , can removed; it's prefix of index1. won't decrease query time, though.

the explain output shows real culprit: "using temporary, using filesort". can avoid having mysql perform ordering same key used join. if change index1 (meeting_id, datetime), might able so. if need keep item_type in index1, either add third column in index, or include in() clause in query values.


Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more