Changeset 7157


Ignore:
Timestamp:
Apr 20, 2018, 1:05:26 PM (3 years ago)
Author:
mabarracus
Message:

Optimized queries

File:
1 edited

Legend:

Unmodified
Added
Removed

  • lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server/analytics/db.php

    r7156 r7157  
    398398        }
    399399        // memory
    400         $sql_2G = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where mem < 2048000 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";
     400        $sql_2g = "select year(date) as year,month(date) as month,Releases_name as rel,count(uuid) as count from Client_Versions where mem < 2048000 and date >= DATE_SUB(NOW(),interval 1 year) group by year,month,rel order by year  desc,month desc,rel asc";
    401401        $cache_key='extended_memory_2G';
    402402        $data = $cache->get($cache_key);
    … …  
    422422            }
    423423        }
    424         $sql_4G = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where mem > 2048000 and mem < 4096000 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";
     424        $sql_4G = "select year(date) as year,month(date) as month,Releases_name as rel,count(uuid) as count from Client_Versions where mem > 2048000  and mem < 4096000 and date >= DATE_SUB(NOW(),interval 1 year) group by year,month,rel order by year  desc,month desc,rel asc";
    425425        $cache_key='extended_memory_4G';
    426426        $data = $cache->get($cache_key);
    … …  
    446446            }
    447447        }
    448         $sql_8G = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where mem > 4096000 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";
     448        $sql_8G = "select year(date) as year,month(date) as month,Releases_name as rel,count(uuid) as count from Client_Versions where mem > 4096000 and date >= DATE_SUB(NOW(),interval 1 year) group by year,month,rel order by year  desc,month desc,rel asc";
    449449        $cache_key='extended_memory_8G';
    450450        $data = $cache->get($cache_key);
    … …  
    471471        }
    472472        // cpu
    473         $sql_mono = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where ncpu = 1 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";
     473        $sql_mono= "select compact.year,compact.month,compact.rel,ifnull(total,0) as total from (select year,month,rel from (select year,month from (select year(now()) as year union all select year(date_sub(now(),interval 1 year))) anyos inner join (select 1 as month union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12) months where STR_TO_DATE(concat(year,'-',month,'-01'),'%Y-%m-%d') >= date_sub(now(),interval 1 year) and now() >= STR_TO_DATE(concat(year,'-',month,'-01'),'%Y-%m-%d') order by year desc, month desc) dates inner join (select '15' as rel union all select '16') releases) compact left join (select year(date) as year, month(date) as month, Releases_name as rel, count(uuid) as total from Client_Versions where date >= date_sub(now(),interval 1 year) and ncpu = 1 group by year,month,rel) ct on compact.year = ct.year and compact.month = ct.month and compact.rel = ct.rel";
    474474        $cache_key='extended_memory_cpu_mono';
    475475        $data = $cache->get($cache_key);
    … …  
    485485                while($row=$result->fetch_array(MYSQLI_ASSOC)){
    486486                    $date=$row['year'].'_'.$row['month'];
    487                     $tmp[$date][$row['rel']]=intval($row['count']);
     487                    $tmp[$date][$row['rel']]=intval($row['total']);
    488488                }
    489489                foreach ($tmp as $date){
    … …  
    495495            }
    496496        }
    497         //$sql_dualquad = "select year(date) as year, month(date) as month, Releases_name, count(Client_uid) as count from Client_Versions where ncpu > 1 and ncpu < 5 group by Releases_name, year, month order by year desc,month desc,Releases_name asc;";
    498         $sql_dualquad = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where ncpu > 1 and ncpu < 5 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";
     497        $sql_dualquad = "select compact.year,compact.month,compact.rel,ifnull(total,0) as total from (select year,month,rel from (select year,month from (select year(now()) as year union all select year(date_sub(now(),interval 1 year))) anyos inner join (select 1 as month union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12) months where STR_TO_DATE(concat(year,'-',month,'-01'),'%Y-%m-%d') >= date_sub(now(),interval 1 year) and now() >= STR_TO_DATE(concat(year,'-',month,'-01'),'%Y-%m-%d') order by year desc, month desc) dates inner join (select '15' as rel union all select '16') releases) compact left join (select year(date) as year, month(date) as month, Releases_name as rel, count(uuid) as total from Client_Versions where date >= date_sub(now(),interval 1 year) and ncpu > 1 and ncpu < 5 group by year,month,rel) ct on compact.year = ct.year and compact.month = ct.month and compact.rel = ct.rel";
    499498        $cache_key='extended_memory_cpu_dualquad';
    500499        $data = $cache->get($cache_key);
    … …  
    510509                while($row=$result->fetch_array(MYSQLI_ASSOC)){
    511510                    $date=$row['year'].'_'.$row['month'];
    512                     $tmp[$date][$row['rel']]=intval($row['count']);
     511                    $tmp[$date][$row['rel']]=intval($row['total']);
    513512                }
    514513                foreach ($tmp as $date){
    … …  
    520519            }
    521520        }
    522         $sql_more = "select year(t1.date) as year, month(t1.date) as month, t1.Releases_name as rel, count(t2.uuid) as count from Client_Versions t1 left join (select * from Client_Versions where ncpu > 4 ) t2 on t1.uuid = t2.uuid group by year, month, rel order by year desc,month desc,rel asc;";
     521        $sql_more = "select compact.year,compact.month,compact.rel,ifnull(total,0) as total from (select year,month,rel from (select year,month from (select year(now()) as year union all select year(date_sub(now(),interval 1 year))) anyos inner join (select 1 as month union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12) months where STR_TO_DATE(concat(year,'-',month,'-01'),'%Y-%m-%d') >= date_sub(now(),interval 1 year) and now() >= STR_TO_DATE(concat(year,'-',month,'-01'),'%Y-%m-%d') order by year desc, month desc) dates inner join (select '15' as rel union all select '16') releases) compact left join (select year(date) as year, month(date) as month, Releases_name as rel, count(uuid) as total from Client_Versions where date >= date_sub(now(),interval 1 year) and ncpu > 4 group by year,month,rel) ct on compact.year = ct.year and compact.month = ct.month and compact.rel = ct.rel";
    523522        $cache_key='extended_memory_cpu_other';
    524523        $data = $cache->get($cache_key);
    … …  
    534533                while($row=$result->fetch_array(MYSQLI_ASSOC)){
    535534                    $date=$row['year'].'_'.$row['month'];
    536                     $tmp[$date][$row['rel']]=intval($row['count']);
     535                    $tmp[$date][$row['rel']]=intval($row['total']);
    537536                }
    538537                foreach ($tmp as $date){
    … …  
    545544        }
    546545         // CLIENT UPDATES
    547          $sql="select year,month,count(*) as nclients,sum(cnt)-count(*) as nclients_updated from (select Client_uid,count(Client_uid) as cnt,year(date) as year,month(date) as month from Client_Versions where date >= '$min_date' GROUP by Client_uid,year,month having count(Client_uid) >= 1 ) t group by year,month order by year desc,month desc";
    548546         $sql="select year,month,count(*) as nclients,sum(cnt)-count(*) as nclients_updated,Releases_name as rel,Flavours_name as fla from (select Client_uid,count(Client_uid) as cnt,year(date) as year,month(date) as month,Releases_name,Flavours_name from Client_Versions where date >= '$min_date' GROUP by Client_uid,year,month,Releases_name,Flavours_name having count(Client_uid) >= 1 ) t group by year,month,Releases_name,Flavours_name order by year desc,month desc";
    549547
    … …  
    624622
    625623
    626          //$sql= "select ltsp,count(*) as total from Client_Versions group by ltsp";
    627624         $sql="select compact.year as year,compact.month as month,if(compact.type='NONE',null,compact.type) as type,ifnull(results.total,0) as total from (select * from
    628625( SELECT * FROM ( SELECT * FROM ( SELECT 1 AS month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 ) t INNER JOIN ( SELECT YEAR(NOW()) AS year UNION ALL SELECT YEAR( DATE_SUB(NOW(), INTERVAL 1 YEAR)) ) t2 ) dates WHERE str_to_date(concat(year,'-',month,'-01'),'%Y-%m-%d') >= DATE_SUB(NOW(),INTERVAL 1 YEAR) and str_to_date(concat(year,'-',month,'-01'),'%Y-%m-%d') <= now() GROUP BY YEAR, MONTH ORDER BY YEAR DESC, MONTH DESC ) last_times inner join (select 'NONE' as type union all select 0 union all select 1) types) compact left join (select month(date) as month,year(date) as year,ifnull(ltsp,'NONE') as type,count(ifnull(ltsp,1)) as total from Client_Versions where Flavours_name = 'client' group by year(date),month(date),ltsp order by year(date) desc ,month(date) desc, ltsp desc ) results on compact.month = results.month and compact.year = results.year and compact.type = results.type";
    629          //$count_ltsp=array('null'=>0,'true'=>0,'false'=>0);
    630626         $count_ltsp=array();
    631627         $cache_key='extended_5';
    … …  
    649645         }
    650646         $cache_key='extended_6';
    651          //$sql = "select t1.mode,count(t2.uuid) as total from (select 'THIN' as mode union all select 'SEMI' UNION ALL select 'FAT') t1 left join (select uuid,mode from Client_Versions where Flavours_name = 'client') t2 on t1.mode = t2.mode group by t1.mode";
    652647         $sql= "select compact.year as year,compact.month as month,if(compact.mode='NONE',null,compact.mode) as mode,ifnull(results.total,0) as total from (select * from ( SELECT * FROM ( SELECT * FROM ( SELECT 1 AS month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 ) t INNER JOIN ( SELECT YEAR(NOW()) AS year UNION ALL SELECT YEAR( DATE_SUB(NOW(), INTERVAL 1 YEAR)) ) t2 ) dates WHERE str_to_date(concat(year,'-',month,'-01'),'%Y-%m-%d') >= DATE_SUB(NOW(),INTERVAL 1 YEAR) and str_to_date(concat(year,'-',month,'-01'),'%Y-%m-%d') <= now() GROUP BY YEAR, MONTH ORDER BY YEAR DESC, MONTH DESC ) last_times inner join (select 'NONE' as mode union all select 'THIN' union all select 'SEMI' union all select 'FAT') types) compact left join (select month(date) as month,year(date) as year,ifnull(mode,'NONE') as mode,count(ifnull(mode,1)) as total from Client_Versions where Flavours_name = 'client' group by year(date),month(date),mode order by year(date) desc ,month(date) desc, mode desc ) results on compact.month = results.month and compact.year = results.year and compact.mode = results.mode";
    653648         $data= $cache->get($cache_key);
    654649         $stime=microtime(true);
    655          //$count_mode=array('thin'=>0,'semi'=>0,'fat'=>0);
    656650         $count_mode=array();
    657651         if ($data != false){
Note: See TracChangeset for help on using the changeset viewer.