MySQL - Optimization select query -



MySQL - Optimization select query -

my query taking 5 seconds finish sounds much basic. did explain query:

https://www.dropbox.com/s/18pedm9n5fssz4e/localhost%20_%20localhost%20_%20logrede%20_%20phpmyadmin%203.4.11.pdf

it possible help me optimize it?

thanks :)

i've restructured query using joins instead of less readable clause. see based on primary "material" table. so, first suggestion utilize "straight_join" clause tell mysql query in order have listed.... otherwise, tries think , things in alternate "primary" table driving query. other tables "lookup values" tables.

secondly... on each of tables, have next indexes on tables. i'm sure had on primary keys of each table... since group_concat getting corresponding descriptive field table, include column in index. here's other reason... when query run , using index, if query not have go raw table "other" columns, doesn't have to. so, having description part of index, can bring together , description both index...

i have index on

material ( cdomaterial ) cliente ( idcliente, nome ) classeobra ( idclasse, sigla ) dma ( iddma, nome ) desenho( iddesenho, nome ) fornecedor ( idfornecedor, nome ) modelo ( idmodelo, nome ) fabricante ( idfabri, nome ) fornecido( codmaterial, preco ) unidade( idunidade, sigla ) tipofornecimento( idtipoforn, sigla )

so, above said, run next query...

select straight_join material.codmaterial, cliente.nome cliente, tipofornecimento.sigla fornecimento, unidade.sigla unidade, group_concat(distinct classeobra.sigla order classeobra.idclasse separator ', ') classeobra, group_concat(distinct dma.nome order dma.iddma separator ', ') dma, group_concat(distinct desenho.nome order desenho.iddesenho separator ', ') desenho, group_concat(distinct fornecedor.nome order fornecedor.idfornecedor separator ', ') fornecedor, group_concat(distinct modelo.nome order modelo.idmodelo separator ', ') modelo, group_concat(distinct fabricante.nome order fabricante.idfabri separator ', ') marca, group_concat(distinct fornecido.preco order fornecido.preco separator ', ') preco material bring together unidad on material.idunidade = unidade.idunidade bring together requisitado on material.codmaterial = requisitado.codmaterial bring together cliente on requisitado.idcliente = cliente.idcliente bring together tipofornecimento on requisitado.idtipoforn = tipofornecimento.idtipoforn bring together possuimodelo on material.codmaterial = possuimodelo.codmaterial bring together modelo on possuimodelo.idmodelo = modelo.idmodelo bring together pertence, on modelo.idmodelo = pertence.idmodelo bring together fabricante on pertence.idfabri = fabricante.idfabri bring together utilizadoclasseobra on material.codmaterial = utilizadoclasseobra.codmaterial bring together classeobra on utilizadoclasseobra.idclasse = classeobra.idclasse bring together possuidma on material.codmaterial = possuidma.codmaterial bring together dma on possuidma.iddma = dma.iddma bring together fornecido on material.codmaterial = fornecido.codmaterial bring together fornecedor on fornecido.idfornecedor = fornecedor.idfornecedor bring together possuidesenho on material.codmaterial = possuidesenho.codmaterial bring together desenho on possuidesenho.iddesenho = desenho.iddesenho grouping material.codmaterial, cliente.nome order material.codmaterial asc, cliente.nome asc ;

mysql optimization

Comments

Post a Comment

Popular posts from this blog

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer -

web services - java.lang.NoClassDefFoundError: Could not initialize class net.sf.cglib.proxy.Enhancer - when trying request rest service @ time of response getting exception.. feb 21, 2013 2:34:49 pm com.sun.jersey.spi.container.containerresponse mapmappablecontainerexception severe: exception contained within mappablecontainerexception not mapped response, re-throwing http container java.lang.noclassdeffounderror: not initialize class net.sf.cglib.proxy.enhancer @ org.hibernate.proxy.pojo.cglib.cgliblazyinitializer.getproxyfactory(cgliblazyinitializer.java:117) @ org.hibernate.proxy.pojo.cglib.cglibproxyfactory.postinstantiate(cglibproxyfactory.java:43) @ org.hibernate.tuple.entity.pojoentitytuplizer.buildproxyfactory(pojoentitytuplizer.java:162) @ org.hibernate.tuple.entity.abstractentitytuplizer.<init>(abstractentitytuplizer.java:135) @ org.hibernate.tuple.entity.pojoentitytuplizer.<init>(pojoentitytuplizer.java:55) @ org.hibernate.tuple.e...
Read more

Accessing MATLAB's unicode strings from C -

Accessing MATLAB's unicode strings from C - how can access underlying unicode info of matlab strings through matlab engine or mex c interfaces? here's example. let's set unicode characters in utf-8 encoded file test.txt, read as fid=fopen('test.txt','r','l','utf-8'); s=fscanf(fid, '%s') in matlab. now if first feature('defaultcharacterset', 'utf-8') , c engevalstring(ep, "s") , output text file utf-8. proves matlab stores unicode internally. if mxarraytostring(enggetvariable(ep, "s")) , unicode2native(s, 'latin-1') give me in matlab: non-latin-1 characters replaced character code 26. need getting access underlying unicode info c string in unicode format (utf-8, utf-16, etc.), , preserving non-latin-1 characters. is possible? my platform os x, matlab r2012b. addendum: documentation explicitly states "[mxarraytostring()] supports multibyte encoded characters...
Read more

javascript - mongodb won't find my schema method in nested container -

javascript - mongodb won't find my schema method in nested container - i trying access method of schema stored within mixed container. here situation : have cases model can many different things, have schema each of these things stored in "casecontent" mixed property. var caseschema = mongoose.schema({ casecontent : {}, object : {type:string, default : "null"}, collision : {type : boolean, default : false} }); the casecontent property filled model of 1 of schemas, 1 exemple : var treeschema = new mongoose.schema({ applecount : {type : number, default : 3} }); treeschema.methods.dostuff = function (data) { console.log('hey, listen'); homecoming true; }; then, want utilize method of schema original container : caseschema.methods.dostuff = function (data) { if (this.casecontent.dostuff !== undefined) { this.casecontent.dostuff(); console.log('it worked'); } else { ...
Read more