Trying To Find The Cause Of Very Slow LEFT JOIN MySQL Query -

-

i appreciate trying find cause of slow return times on basic query table joins.

i having trouble turned on profiling , query took 19.7903 sec return showed following profile details:

profiling status  time starting    0.000044 opening tables  0.000067 system lock     0.000002 table lock  0.000006 init    0.000009 optimizing  0.000005 statistics  0.000011 preparing   0.000014 executing   0.000031 sending data    0.000050 end     0.000004 end     0.000003 query end   0.000002 freeing items   0.000009 closing tables  0.000003 removing tmp table  0.000011 closing tables  0.000003 logging slow query  0.000002 cleaning     0.000003  showing rows 0 - 29 (2,200 total, query took 19.7903 sec)

i don't understand why profile times not add "19.7903 sec".

  • are 'profile' time , 'total, query' time, add up?<<<<<<<

the query was:

select owner.id                                         owner_id,        owner.last_name                                  owner_last_name,        owner.first_name                                 owner_first_name,        owner.phone_home_areacode                        owner_phone_home_areacode,        owner.phone_home_prefix                          owner_phone_home_prefix,        owner.phone_home_line_number                     owner_phone_home_line_number,        owner.phone_cell_areacode                        owner_phone_cell_areacode,        owner.phone_cell_prefix                          owner_phone_cell_prefix,        owner.phone_cell_line_number                     owner_phone_cell_line_number,        /*some columns owner removed brevity*/        owner.city                                       owner_city,        owner.province                                   owner_province,        owner.postal                                     owner_postal,        owner.notes                                      owner_notes,        owner.referred_by                                owner_referred_by,        visitor.name                                     visitor_name,        visitor.size                                     visitor_size,        visitor.inactive                                 visitor_inactive,        visitor.id                                       visitor_id,        visitor.deleted                                  visitor_deleted,        vaccinations.canine_distemper_parvovirus_expires vaccinations_canine_distemper_parvovirus_expires,        vaccinations.canine_rabies_expires               vaccinations_canine_rabies_expires,        vaccinations.canine_bordetella_expires           vaccinations_canine_bordetella_expires,        vaccinations.feline_fvrcpc_expires               vaccinations_feline_fvrcpc_expires,        vaccinations.feline_rabies_expires               vaccinations_feline_rabies_expires   owner        left join visitor          on visitor.owner_id = owner.id        left join vaccinations          on vaccinations.visitor_id = visitor.id order  visitor_name

result of explain:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    1   simple  owner       null    null    null    null    1483    using temporary; using filesort 1   simple  visitor     ref     owner_id    owner_id    4   waydev.owner.id     1     1   simple  vaccinations        visitor_id  null    null    null    2059

indexes:

visitor: indexes: documentation keyname  type    cardinality     action  field primary     primary     2227    edit    drop    id name    index   2227    edit    drop    name owner_id    index   2227    edit    drop    owner_id    owner: indexes: documentation keyname  type    cardinality     action  field primary     primary     1601    edit    drop    id last_name   index   1601    edit    drop    last_name   vaccinations: indexes: documentation keyname  type    cardinality     action  field primary     primary     2131    edit    drop    id visitor_id  unique  2131    edit    drop    visitor_id   boarding: indexes: documentation keyname  type    cardinality     action  field primary     primary     2256    edit    drop    id owner   index   2256    edit    drop    owner_id visitor     index   2256    edit    drop    visitor_id   grooming: indexes: documentation keyname  type    cardinality     action  field primary     primary     2077    edit    drop    id visitor_id  unique  2077    edit    drop    visitor_id owner   index   2077    edit    drop    owner_id

this query has performed fine months. , intermittently comes slow. 20% of time slow. rest of time returns fine (showing rows 0 - 29 (2,200 total, query took 0.0018 sec)). ? :\

  • could fact intermittent problem indicate problem other query itself??<<<<<<<

    as ask above... don't understand why profile times not add "19.7903 sec".

  • are 'profile' time , 'total, query' time, add up? <<<<<<<

only few thousand records in entire database.

any appreciated. i'm on godaddy server.

(i understand there potentially wrong query.. or optimized. asking pretty specific questions here - noted the... "<<<<<<<")

honestly... talking >>>>>>>20<<<<<<< seconds here! surely query on 2000 record database should not coming 20 seconds???

the problem order field second table in left join. no index can effifiently used ordering, mysql orders rows whole lots of data in each row. usual solution in mysql query select ids , ordering within subquery , join rest of colums. case when paginagion (limit shoul within subquery).

you need composite covering index (owner_id, name) on visitor, , (visitor_id) on vaccinations.

now rewrite query following:

select ... (     select o.id o_id, v.id v_id     owner o     left join visitor v on o.id = v.owner_id     order v.name ) ids join owner o on o.id = ids.o_id left join visitor v on v.id = ids.v_id left join vaccinations v2 on v2.visitor_id = v.id;

Comments

Post a Comment

Popular posts from this blog

c# - How Configure Devart dotConnect for SQLite Code First? -

-
can configure following sample code ? when run following code, error : no entity framework provider found ado.net provider invariant name 'devart.data.sqlite' setting in machine.config <system.data> <dbproviderfactories> <add name="dotconnect sqlite" invariant="devart.data.sqlite" description="devart dotconnect sqlite" type="devart.data.sqlite.sqliteproviderfactory, devart.data.sqlite, version=4.6.287.0, culture=neutral, publickeytoken=09af7300eec23701" /> </dbproviderfactories> </system.data> after add below block code in app.config <?xml version="1.0"?> <configuration>   <configsections> <!-- more information on entity framework configuration, visit http://go.microsoft.com/fwlink/?linkid=237468 --> <section name="entityframework" type="system.data.entity.internal.configfile.entityframeworksection, entit...
Read more

java - Copying object fields -

-
what best way of copying classes when need have 2 independent variables? have simple class: public class myclass { boolean = false; string b= "not empty"; } do need make method : assign(myclass data ) { a= data.a; b= data.b; } is there automatic method copy (duplicate) objects in java? do need make method : pretty close. instead of making method, should make constructor. such constructors called copy constructor , , create them this: myclass(myclass data) { = data.a; b = data.b; } and create copy of instance, use constructor this: myclass obj1 = new myclass(); myclass obj2 = new myclass(obj1); copy constructor can tedious : using copy constructor create deep-copy can tedious, when class has mutable fields. in case, assignment create copy of reference, , not object itself. have create copy of fields (if want deep copy). can go recursive. a better way create deep copy serialize , deserialize object. why not use clone()? ...
Read more

c++ - Clear the memory after returning a vector in a function -

-
i have function processes , stores lot of data in , returns results vector of class. amount of data stored in function tremendous , want clear storage memory of function after finished job. necessary (does function automatically clear memory) or should clear memory function? update: vector<customers> process(char* const *filename, vector<int> id) { vector<customers> list_of_customers; (perform actions) return list_of_customers; } variables defined locally within function automatically released @ end of function scope. destructors objects called, should free memory allocated objects (if objects coded correctly). example of such object std::vector . anything you've allocated new must have corresponding delete release storage. try avoid doing own allocation , use raii instead, i.e. containers or smart pointers.
Read more