query optimization - MySQL: "Sending data" vs "Copying to tmp table" -


i have 2 queries same thing , give same result set, writing differently. looking @ profile, take same time execute: around 0.075 seconds

the fascinating thing query#1 takes of entire process execution time in state "copying tmp table". query#2 takes entire process execution time in state "sending data".

so, question is:

what state better mysql query spent of time, "sending data" or "copying tmp table"?

query#1 (spends execution time in state "copying tmp table")

select ac.album_category_id, ac.name, ac2.album_category_id, ac2.name, ac2.description, t2.total album_category ac join album_category ac2 on ac2.parent_album_category_id = ac.album_category_id join (     select a.album_category_id cat_id, sum(result.count_image) total     album     join (         select ifnull(a.parent_album_id, a.album_id) p_album_id, count(ai.image_id) count_image         album_image ai         join album on a.album_id = ai.album_id         join image on i.image_id = ai.image_id         i.is_listed = 1         group p_album_id     ) result on result.p_album_id = a.album_id     group a.album_category_id ) t2 on t2.cat_id = ac2.album_category_id order ac.position, ac2.position

query#2 (spends execution time in state "sending data")

select ac.album_category_id, ac.name, ac2.album_category_id, ac2.name, ac2.description, t3.total album_category ac join album_category ac2 on ac2.parent_album_category_id = ac.album_category_id join (     select a2.album_category_id cat_id, sum(t2.sum_image) total     album a2     join (         select ifnull(a.parent_album_id, a.album_id) p_album_id, sum(t1.count_image) sum_image         album         join (             select ai.album_id album_id, count(ai.image_id) count_image             album_image ai             join image on i.image_id = ai.image_id             i.is_listed = 1             group ai.album_id         ) t1 on t1.album_id = a.album_id         group p_album_id     ) t2 on t2.p_album_id = a2.album_id     group a2.album_category_id ) t3 on t3.cat_id = ac2.album_category_id order ac.position, ac2.position

edit

as per jkavalik request, i've added explain. i'm not sure how add in text, i've added images instead. hope that's ok.

explain query #1

enter image description here

explain query #2 enter image description here

copying tmp table: server copying temporary table in memory.

sending data: thread reading , processing rows select statement, , sending data client. because operations occurring during state tend perform large amounts of disk access (reads), longest-running state on lifetime of given query.

http://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html

sending data happen on queries query state change fast on quick queries won't notice it. however, since noticing it, there may lot of io going on @ time. when run query, in terminal window @ iostat -x 10 , watch disk io wait (assuming using linux/unix). if wait shoots up, mysql reading disk, doing work , preparing push data client.

copying tmp table means result of part of query or subquery moved memory more work can done using temporary data (temp table). whenever have encountered state, there's possibility of optimization. i'd more concerned both states if query taking several seconds return. i'd optimize query 1 in case. might want try increasing innodb_buffer_pool_size (assuming using innodb engine)

https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html


Comments

Post a Comment

Popular posts from this blog

1111. appearing after print sequence - php -

morning, i have code takes string of shoe sizes, explodes , puts in table. code works , info displayed correctly on page after table there few 1's after table. i'm confused this. so table looks (in columns of 3 on webpage) available in following sizes: 3 4 5 6 9 10 111 here's codes, purpose of example $x = 3|4|6|10|9 function get_sizes_pipe($x) { $counter = 0; $splits = explode("|",$x); asort($splits); $x = print "<table class='greentable'>"; $x .= print "<thead><tr><th scope='col' colspan='3' abbr='starter'>available in following sizes:</th></tr></thead><tbody><tr>"; foreach ($splits $split) { $entry = print "<td>".$split."</td>"; if($counter == 2){ $entry .= print "</tr><tr>"; $counter =0; } else { $counter++; ...
Read more

java - WARN : org.springframework.web.servlet.PageNotFound - No mapping found for HTTP request with URI [/board/] in DispatcherServlet with name 'appServlet' -

this question has answer here: why spring mvc respond 404 , report “no mapping found http request uri […] in dispatcherservlet”? 3 answers i trying making simple bbs i'm encountering following error, warn : org.springframework.web.servlet.pagenotfound - no mapping found http request uri [/board/] in dispatcherservlet name 'appservlet' i have tried solve error many times, have failed. how can solve error? here web.xml, controllerand , servlet-context. my web.xml <?xml version="1.0" encoding="utf-8"?> <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/xmlschema-instance" xsi:schemalocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"> <filter> <filter-nam...
Read more

Ruby on Rails, ActiveRecord, Postgres, UTF-8 and ASCII-8BIT encodings -

weirdest error, who’s challenge try , me? spent hours yesterday on , it’s magic. can't believe happening , it's driving me crazy. btw, using: rails 4.2.3 ruby 2.2.3 pg 0.18 encoding.default_internal = encoding.default_external = utf-8 config.encoding = 'utf-8' it has postgres, activerecord , encodings! turns out we’ve been getting these errors whenever create new users on database special characters: encoding::undefinedconversionerror: "\xc3" ascii-8bit utf-8 \xc3 may vary depending on characters. weird part we’ve set since beginning utf-8. makes no sense , digged it, , used script: user.all.each |user| user.attributes.each |name, value| if value.is_a? string puts user.email + name.encoding.to_s + value.encoding.to_s end end end and output of 1 of users, users same: dr_lottahelp@blahblah.comutf-8utf-8 dr_lottahelp@blahblah.comutf-8ascii-8bit dr_lottahelp@blahblah.comutf-8ascii-8bit dr_lottahelp@blahblah.comutf-8asci...
Read more