sql - Mysql query with indexes take a long time -


i have table in mysql, has 11 millions rows; select request use indexes, where, temporary , filesort

select sql_no_cache          sum(t.requests) requests,         sum(t.impression) impression,         sum(t.double_imp) double_imp,         sum(t.bad_requests) bad_requests,         sum(t.empty_body) empty_body,         sum(t.bad_vast) bad_vast,         sum(t.exceptions) exceptions,         sum(t.midpoint) midpoint,         sum(t.thirdquartile) thirdquartile,         sum(t.complete) complete,         sum(t.click) click,         sum(t.start) start,         sum(t.error) error,         sum(t.creativeview) creativeview,         sum(if(t.rate_type = "0", (t.impression * t.fixed_rate) / 1000, (t.impression * t.rate) / 1000 * (t.percent_rate / 100))) media_cost,         sum((t.impression * t.rate) / 1000) revenue,         t.date         stats  t                 t.date >= "2017-08-14 00:00:00" ,         t.tag_id = 185         group t.date

when try explain query i've got next:

+----+-------------+-------+------------+------+--------------------+---------------+---------+-------+---------+----------+---------------------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys      | key           | key_len | ref   | rows    | filtered |                                                               | +----+-------------+-------+------------+------+--------------------+---------------+---------+-------+---------+----------+---------------------------------------------------------------------+ |  1 | simple      | t     | null       | ref  | date,supply_tag_id | supply_tag_id | 5       | const | 5372331 |    50.00 | using index condition; using where; using temporary; using filesort | +----+-------------+-------+------------+------+--------------------+---------------+---------+-------+---------+----------+---------------------------------------------------------------------+

i have send report tag_id, domain, country , using date filter. it's take long time.

result:

+----------+------------+------------+--------------+------------+----------+------------+----------+---------------+----------+-------+--------+--------+--------------+--------------------+--------------------+---------------------+ | requests | impression | double_imp | bad_requests | empty_body | bad_vast | exceptions | midpoint | thirdquartile | complete | click | start  | error  | creativeview | media_cost         | revenue            | date                | +----------+------------+------------+--------------+------------+----------+------------+----------+---------------+----------+-------+--------+--------+--------------+--------------------+--------------------+---------------------+ |  1271160 |      90531 |         46 |       304032 |      99560 |        0 |          0 |    65379 |         61777 |    68897 |   458 | 200359 | 440547 |       200197 | 167.78620000647717 | 335.57240001295435 | 2017-08-14 00:00:00 | |  1253073 |      93511 |          7 |       257968 |      96868 |        0 |          0 |    62792 |         59275 |    69590 |   480 | 200685 | 414717 |       200520 | 174.01877502643288 | 348.03755005286575 | 2017-08-14 01:00:00 | |  1174477 |      77541 |         10 |       220188 |      86333 |        0 |          0 |    52267 |         49380 |    57340 |   442 | 190527 | 362037 |       190356 | 145.73635006021442 | 291.47270012042884 | 2017-08-14 02:00:00 | |  1122816 |      63555 |          5 |       259560 |      81427 |        0 |          0 |    44284 |         41839 |    47567 |   340 | 156377 | 359633 |       156245 | 119.94110007583329 | 239.88220015166658 | 2017-08-14 03:00:00 | |  1193945 |      46442 |          5 |       255575 |      81628 |        0 |          0 |    30905 |         29189 |    34025 |   263 | 132637 | 388845 |       132527 |  86.10265007089804 | 172.20530014179607 | 2017-08-14 04:00:00 | |  1301825 |      72943 |         12 |       269850 |      92438 |        0 |          0 |    46121 |         42915 |    49640 |   545 | 227208 | 405245 |       227060 | 136.62139983045088 | 273.24279966090177 | 2017-08-14 05:00:00 | |  1375913 |      77615 |         14 |       278095 |      99777 |        0 |          0 |    56659 |         52836 |    54024 |   791 | 269526 | 395597 |       269383 |   150.330824404467 |   300.661648808934 | 2017-08-14 06:00:00 | |  1592128 |      40945 |          6 |       318811 |     106792 |        0 |          0 |    28313 |         26365 |    28708 |   388 | 269652 | 478694 |       269525 |  77.05547480582607 | 154.11094961165213 | 2017-08-14 07:00:00 | |  2085863 |      70902 |         16 |       684129 |     144990 |        0 |          0 |    46282 |         43673 |    52301 |   416 | 301956 | 818018 |       301753 | 126.72110009752994 | 253.44220019505988 | 2017-08-14 08:00:00 | |  1530312 |      56250 |         10 |       451030 |     105760 |        0 |          0 |    33535 |         31578 |    41443 |   258 | 191312 | 638500 |       191187 |  99.43862510215726 | 198.87725020431452 | 2017-08-14 09:00:00 | +----------+------------+------------+--------------+------------+----------+------------+----------+---------------+----------+-------+--------+--------+--------------+--------------------+--------------------+---------------------+ 10 rows in set (46.07 sec)

this table structure:

create table `stats` (   `date` datetime not null,   `tag_id` int(11) default null,   `domain` int(11) default null,   `country` int(11) default null,   `rate` float default '0',   `percent_rate` float default '0',   `fixed_rate` float default '0',   `rate_type` int(11) not null default '0',   `requests` int(11) default '0',   `bad_requests` int(11) default '0',   `empty_body` int(11) default '0',   `bad_vast` int(11) default '0',   `exceptions` int(11) default '0',   `start` int(11) default '0',   `firstquartile` int(11) default '0',   `midpoint` int(11) default '0',   `thirdquartile` int(11) default '0',   `complete` int(11) default '0',   `close` int(11) default '0',   `pause` int(11) default '0',   `resume` int(11) default '0',   `acceptinvitationlinear` int(11) default '0',   `timespentviewin` int(11) default '0',   `otheradinteraction` int(11) default '0',   `progress` int(11) default '0',   `creativeview` int(11) default '0',   `mute` int(11) default '0',   `unmute` int(11) default '0',   `fullscreen` int(11) default '0',   `impression` int(11) default '0',   `player_imp` int(11) not null default '0',   `double_imp` int(11) not null default '0',   `error` int(11) default '0',   `no_show` int(11) not null default '0',   `budget_limit` int(11) not null default '0',   `click` int(11) default '0',   `nonlinearclickthrough` int(11) default '0',   `companionclickthrough` int(11) default '0',   key `date` (`date`),   key `tag_id` (`tag_id`),   key `domain` (`domain`),   key `country` (`country`) ) engine=innodb default charset=utf8

please optimize table , query;

the query uses equal operator tag_id = 185 , range operator date >= in clause:

      t.date >= "2017-08-14 00:00:00" ,     t.tag_id = 185

so speed conrete query, create multicolumn index on tag_id, t.date (in particular order - column = operator must first in index), is:

create index somename on ( tag_id, t.date );

Comments

Post a Comment

Popular posts from this blog

PHP and MySQL WP -

i trying accomplish 2 things here code. writing php directly wp page using insert php plugin. 1) query mysql database , return results 1 column 4 columns. 2) make each result own hyperlink directs user new wp page runs new query on page. i able query show results , turn results dynamic hyperlink, cannot formatting down. right returns result 1 column. here have: [insert_php] $servername = "localhost"; $username = "login"; //edited privacy $password = "password"; //edited privacy $database = "database"; //edited privacy // create connection $conn = new mysqli($servername, $username, $password, $database); // check connection if ($conn->connect_error) { die("connection failed: " . $conn->connect_error); } echo "connected successfully"; //get results database $result = mysqli_query($conn,"select mine mines order mine"); $all_property = array(); //declare array saving property //showing proper...
Read more

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

go - golang pprof for c library code -

i have been trying profile golang application pprof, seems details of c code(linked static library) missed in output result. flame graph from frame graph can see c code time consuming reported within "runtime._externalcode", no details @ all. , sure static library compiled debug enable. so there way make pprof details(like stack trace) of 3rd party c code? or there other golang profiling tool this?
Read more