database - MySQL query takes ages to return on a huge table -

-

i have big table , following code takes 990 sec. complete. bdate , itype indexed. else need optimize/change?

select s, count(*) total  `mt_ex_15`  bdate > '2014-10-01' , bdate < '2014-11-01' , itype = '3' group s order total desc

edit: here explain

id  select_type table   type    possible_keys   key key_len ref rows       1   simple  mt_ex_15    ref itype,bdate,s   itype   2   const   44157686    using where; using temporary; using filesort

edit: think need optimize db or my.cnf because following query took 40 secs.

select count(*) total  `mt_ex_15`  bdate > '2015-02-01' , bdate < '2015-03-01'

and here explain:

 id     select_type     table   type    possible_keys   key     key_len     ref     rows       1   simple  mt_ex_15    range   bdate   bdate   3   null    4494019     using where; using index

for query:

select s, count(*) total  `mt_ex_15`  bdate > '2014-10-01' , bdate < '2014-11-01' , itype = '3' group s order total desc

the best index mt_ex_15(itype, bdate, s). engine should able take full advantage of index where clause. in addition, covering index original data not need touched query.

if had list of available "s" values, correlated subquery:

select s.*,        (select count(*)         mt_ex_15 m         m.s = s.s , m.itype = 3 , m.bdate > '2014-10-01' , m.bdate < '2014-11-01'        ) total s having total > 0 -- using convenient mysql extension order total desc;

the best index query mt_ex_15(s, itype, bdate).

note: if itype integer, should remove quotes around constant. misleading.


Comments

Post a Comment

Popular posts from this blog

java - Ebean enhancement ignores a model -

-
we using avaje-agentloader enhance our ebeans. ebeans in same package. including loader, agent & base ebean library in our project (via sbt): "org.avaje" % "avaje-agentloader" % "1.1.2", "org.avaje.ebeanorm" % "avaje-ebeanorm" % "4.5.5", "org.avaje.ebeanorm" % "avaje-ebeanorm-agent" % "4.5.2", however, when loader runs enhancement, skips 1 ebean. each ebean annotated @entity , extends com.avaje.ebean.model . there seem no differences between ones enhanced , 1 not. there no includes, or extends, etc. basically, i'm wondering if has run across issue in past, or has insights. things we've tried: specifying actual classes enhanced via serverconfig.setclasses(...) specifying pakages analyse/enhance via serverconfig.addpackage(...) or setpackages(...) not specifying @ , having loader analyse all. we've set break-point @ transformer.transform , bean in questio...
Read more

javascript - Reference error while trying to encapsulate an animation function -

-
i'm trying put function own class object. here below. the canvas property holds dom functions, , getting error next property, ctx, should refer method defined in canvas. so property ctx should refer ctx method defined in canvas property. bt when page loads, dev tools telling me there reference error @ point - canvas not defined. i encounter problem when canvas defined in external global variable. var drawcrash = { canvas : function () { dom methods here }, ctx : canvas.ctx, pointer : canvas.pointer, particles : "", nbrparticles : 160, speed : 6, strength : .4, radius : 4, perspective : 0.5, ang : null, cosorsin1 : null, cosorsin2 : null, //functions setspeed : function (_inputspeed) { this.speed = _inputspeed; }, startdrawing : function () { pointer.down = function() { } } run(); } } i believe canvas not available. don't put line ctx: c...
Read more

SQL php on different pages to Insert (mysqli) -

-
i have piece of php code, works when placed on index page ( index.php ), nothing if put in page, i.e. add.php for example: having code add.php page, tried go to: www.myhost.com/add.php , nothing happen. if copy code index.php , go page www.myhost.com/index.php add row in sql table. why that? <?php $server="mysql.server.com"; $user="randomusr"; $pass="randompwd"; $db="randomdb"; $link=mysqli_connect($server, $user, $pass, $db); $result=mysqli_query($link,"select * `battery` `id`"); $query = "insert battery (id,type,owner,charge,time) values ('1','lily002','maisnow','15','2015-04-22 17:20:28')"; mysqli_query($link, $query); header("location: index.php"); //this 1 comment out when copied index.php ?> l # name type collation attributes null 1 id int(16) no ...
Read more