/adei/trunk

To get this branch, use:
bzr branch http://darksoft.org/webbzr/adei/trunk

« back to all changes in this revision

Viewing changes to classes/readers/dbreader.php

  • Committer: Suren A. Chilingaryan
  • Date: 2020-02-25 03:19:32 UTC
  • Revision ID: csa@suren.me-20200225031932-0nchtkl9fv0v5i0w
To optimize performance of the correponding queries, the way how aggregate functions (MIN,MAX,COUNT) are used with MySQL INNODB is changed

Show diffs side-by-side

added added

removed removed

Lines of Context:
classes/readers/dbreader.php
329
329
            foreach ($this->tables as $re => &$info) {
330
330
                if (preg_match($re, $gid)) {
331
331
                    if (is_array($info)) {
332
 
                        if (is_array($info[0])) {
333
 
                            $minfo = &$info;
334
 
                        } else {
335
 
                            $tmp_info = array (&$info);
336
 
                            $minfo = &$tmp_info;
337
 
                        }
338
 
                        foreach ($minfo as $sinfo) {
 
332
                        if (!is_array($info[0]))
 
333
                            $info = array ($info);
 
334
 
 
335
                        foreach ($info as $sinfo) {
339
336
                            if (isset($sinfo['title'])) {
340
337
                                $name = preg_replace($re, $sinfo['title'], $gid);
341
338
                                $real_gid = preg_replace($re, $sinfo['gid'], $gid);
370
367
 
371
368
        $blob_groups = $this->opts->Get('blob_groups', array());
372
369
 
373
 
 
374
370
        $done_flag = false;
375
371
        $real_gid = array();
376
372
        $members2 = array();
420
416
          $groups[$gid]['gid'] = $gid;
421
417
          $groups[$gid]['name'] = $name;
422
418
 
423
 
 
424
419
          if ((!isset($real_gid[$gid]))&&($flags&REQUEST::NEED_INFO)) {
425
420
            if ($grp) {
426
421
                $grzeus = $grp;
428
423
                $ginfo = array("db_group" => $gid);
429
424
                $grzeus = $this->CreateGroup($ginfo);
430
425
            }
431
 
            
432
 
            $tc = $this->columns['time'];
433
 
            $req = "MIN($tc), MAX($tc)";
434
 
            if ($flags&REQUEST::NEED_COUNT) 
435
 
                $req .= ", COUNT($tc)";
436
426
 
437
427
            if ($this->monitor_timings) {
438
428
                $tt = $this->req->GetGroupOption("monitor_timings", $grp);
444
434
                    $tt_limit = 1000000;
445
435
                    $tt_exception = false;
446
436
                }
447
 
                $tt_limit += 1000000*microtime(true);
 
437
                $microtime_before = microtime(true);
 
438
                $tt_limit += 1000000 * $microtime;
448
439
/*
449
440
                $tod = gettimeofday();
450
441
                $tod['usec'] += $tt_limit%1000000;
460
451
                log_message("Sleeping");
461
452
                usleep($this->emit_delays);
462
453
            }
463
 
 
464
 
            $valres = $this->db->Query("SELECT $req FROM " . $this->db->tbl_quote_l . $grzeus->table . $this->db->tbl_quote_r, DATABASE::FETCH_NUM);
465
 
            $vals = $valres->fetch(PDO::FETCH_NUM);
466
 
            $valres = NULL;
 
454
            
 
455
 
 
456
            $opts = $this->req->GetGroupOptions($grzeus);
 
457
            $limit = $opts->GetDateLimit();
 
458
 
 
459
            $tc = $this->columns['time'];
 
460
            if ((is_numeric($limit[0]))&&((is_numeric($limit[1]))))
 
461
                $condition = $this->db->col_quote_l . $tc . "{$this->db->col_quote_r} BETWEEN " . $this->ImportUnixTime($limit[0]) . " and " . $this->ImportUnixTime($limit[1]);
 
462
            elseif (is_numeric($limit[0]))
 
463
                $condition = $this->db->col_quote_l . $tc . "{$this->db->col_quote_r} >= " . $this->ImportUnixTime($limit[0]);
 
464
            elseif (is_numeric($limit[1]))
 
465
                $condition = $this->db->col_quote_l . $tc . "{$this->db->col_quote_r} <= " . $this->ImportUnixTime($limit[1]);
 
466
            else
 
467
                $condition = "";                
 
468
 
 
469
            $req = "";
 
470
            if (($flags&REQUEST::PREFER_EXACTINFO) == 0)
 
471
                $req = "MIN($tc), MAX($tc)";
 
472
            
 
473
            if ($flags&REQUEST::NEED_COUNT) {
 
474
                if ($req) $req .= ", ";
 
475
                $req .= "COUNT($tc)";
 
476
            }
 
477
 
 
478
                // Too slow: COUNT/MIN/MAX (any of them) queries on subset (>/</BETWEEN) results in temporary table generation with MySQL InnoDB (ordering is not helping).
 
479
            if ($req) $query = "SELECT $req FROM " . $this->db->tbl_quote_l . $grzeus->table . $this->db->tbl_quote_r;
 
480
            else $query = false;
 
481
 
 
482
            if ($flags&REQUEST::PREFER_EXACTINFO) {
 
483
                $sel = array('limit' => 1, 'condition' => $condition);
 
484
                $query_first = $this->db->SelectRequest($grzeus->table, $tc, array_merge($sel, array('order' => "$tc ASC")));
 
485
                $query_last = $this->db->SelectRequest($grzeus->table, $tc, array_merge($sel, array('order' => "$tc DESC")));
 
486
 
 
487
                $vals = array();
 
488
                $valres = $this->db->Query($query_first, DATABASE::FETCH_NUM);
 
489
                $vtmp = $valres->fetch(PDO::FETCH_NUM);
 
490
                if ($vtmp) $vals[0] = $vtmp[0];
 
491
                $valres = $this->db->Query($query_last, DATABASE::FETCH_NUM);
 
492
                $vtmp = $valres->fetch(PDO::FETCH_NUM);
 
493
                if ($vtmp) $vals[1] = $vtmp[0];
 
494
 
 
495
                if ($query) {
 
496
                        // Even without WHERE condition, this is SLOW with INNODB tables. We also can't use SHOW TABLE STATUS for approximates because it not work for VIEWs.
 
497
                        // SHOW VIEW permission is needed for this variant to work.
 
498
                    if ($condition) $condition = " WHERE $condition";
 
499
                    try {
 
500
                        if (!strcasecmp($this->db->driver, "mysql")) {
 
501
                            $valres = $this->db->Query("EXPLAIN $query");
 
502
                            $vtmp = $valres->fetch(PDO::FETCH_ASSOC);
 
503
                            if ($vtmp) $vals[2] = $vtmp['rows'];
 
504
                        } else {
 
505
                            $valres = $this->db->Query("$query", DATABASE::FETCH_NUM);
 
506
                            $vtmp = $valres->fetch(PDO::FETCH_NUM);
 
507
                            if ($vtmp) $vals[2] = $vtmp[0];
 
508
                        }
 
509
                    } catch (PDOException $e) {
 
510
                            // Likely missing SHOW VIEW permission
 
511
                        $vals[2] = "?";
 
512
                    }
 
513
                }
 
514
                $valres = NULL;
 
515
            } else {
 
516
                $valres = $this->db->Query($query, DATABASE::FETCH_NUM);
 
517
                $vals = $valres->fetch(PDO::FETCH_NUM);
 
518
                $valres = NULL;
 
519
            }
467
520
            
468
521
            if ($this->monitor_timings) {
469
 
                if (1000000 * microtime(true) > $tt_limit) {
470
 
                    $msg = translate("The query on group '%s' is exceeded allowed execution time (exceeding %d msec). This normally indicates inappropriate indexing of the source database. You can overcome the problem by setting '%s' and '%s' options", $grp->gid, ceil(1000*microtime(true) - $tt_limit/1000), "use_cache_reader", "fill_raw_first");
 
522
                $microtime = microtime(true);
 
523
                $groups[$gid]['query_time'] = $microtime - $microtime_before;
 
524
                if (1000000 * $microtime > $tt_limit) {
 
525
                    $msg = translate("The query on table '%s' is exceeded allowed execution time (exceeding %d ms). This normally indicates inappropriate indexing of the source database. You can overcome the problem by setting '%s' and '%s' options", $grzeus->table, ceil(1000*$microtime - $tt_limit/1000), "use_cache_reader", "fill_raw_first");
471
526
                    if ($tt_exception)
472
527
                        throw new ADEIException($msg);
473
 
                    else
474
 
                        log_message($this->req->GetLocationString() . ": " . $msg);
 
528
                    // else log_message($this->req->GetLocationString() . ": " . $msg);
475
529
                }
476
530
            }
477
531
 
478
532
            if (($vals)&&(($vals[0])||($vals[1]))) {
479
 
                $opts = $this->req->GetGroupOptions($grzeus);
480
 
                $limit = $opts->GetDateLimit();
481
 
            
482
533
                $groups[$gid]['first'] = $this->ExportUnixTime($vals[0]);
483
 
                if ((is_int($limit[0]))&&($limit[0] > $groups[$gid]['first'])) {
 
534
                if ((is_numeric($limit[0]))&&($limit[0] > $groups[$gid]['first'])) {
484
535
                    $groups[$gid]['first'] = $limit[0];
485
536
                } else if ($groups[$gid]['first'] < 0) {
486
537
                    $groups[$gid]['first'] = 0;
487
538
                }
488
539
            
489
540
                $groups[$gid]['last'] = $this->ExportUnixTime($vals[1]);
490
 
                if ((is_int($limit[1]))&&($limit[1] < $groups[$gid]['last'])) {
 
541
                if ((is_numeric($limit[1]))&&($limit[1] < $groups[$gid]['last'])) {
491
542
                    $groups[$gid]['last'] = $limit[1];
492
543
                } else if ($groups[$gid]['last'] < 0) {
493
544
                    $groups[$gid]['last'] = 0;
511
562
            unset($extra_info['id']);
512
563
            $groups[$gid] = array_merge($groups[$gid], $extra_info);
513
564
        }
514
 
 
515
565
    }
516
566
 
517
567
    return $grp?$groups[$grp->gid]:$groups;