mysql - Slow query with lot of joins and 'IN' statement -


i have slow query , believe due 'in' statement. list of ids in 'in' statement can large, 250+. query run 2+ seconds every time, , slow.

is there way speed up?

select u.achternaam achternaam, u.code code, srt.niveaus niveaus, srt.soortid soortid,ifnull( scr.gehaald,'0')as gehaald, ifnull( scr.gespeeld,'0')as gespeeld, if( srt.niveaus =1,'n.v.t.', ifnull(scr.huidigniveau,'0') )as niveautext  gebruikers u  left join ( select scoreid,schoolcode, soortid, userid, gehaald, gespeeld, huidigniveau              scores) scr on scr.userid = u.code               left join ( select soortid,niveaus,spelid,titel,verberg              soorten              soortid in (13,14,15,16,17,561,566,567,568,574,575,1,100,101,102,103,104,105,107,108,109,11,110,112,113,114,115,116,117,118,12,120,121,122,123,124,125,126,127,128,129,130,131,133,134,137,138,140,141,144,145)) srt on scr.soortid = srt.soortid left join(select spelnaam,spelid,vakid spellen) g on srt.spelid=g.spelid left join(select vakid,vaknaam vakken) vak on g.vakid=vak.vakid u.groepsid='28' , u.schoolcode ='1'  , srt.verberg =0 order achternaam,vak.vaknaam,g.spelnaam,srt.titel asc

result of explain select:

explain

don't use subqueries, join tables directly.

note srt.verberg = 0 test should in on clause, not where clause. when use left join, conditions on second table should in on, unless you're testing null results not finding match.

select u.achternaam achternaam, u.code code, srt.niveaus niveaus, srt.soortid soortid,ifnull( scr.gehaald,'0')as gehaald, ifnull( scr.gespeeld,'0')as gespeeld, if( srt.niveaus =1,'n.v.t.', ifnull(scr.huidigniveau,'0') )as niveautext  gebruikers u  left join scores scr on scr.userid = u.code               left join soorten srt on scr.soortid = srt.soortid     , srt.soortid in (13,14,15,16,17,561,566,567,568,574,575,1,100,101,102,103,104,105,107,108,109,11,110,112,113,114,115,116,117,118,12,120,121,122,123,124,125,126,127,128,129,130,131,133,134,137,138,140,141,144,145)     , srt.verberg = 0 left join spellen g on srt.spelid=g.spelid left join vakken vak on g.vakid=vak.vakid u.groepsid='28' , u.schoolcode ='1' order achternaam, vak.vaknaam, g.spelnaam, srt.titel asc

also, make sure need use left join rather inner join. left join should used when want result include rows first table have no matches in second table. inner join performance better, should use whenever appropriate.


Comments

Post a Comment

Popular posts from this blog

python - Installing PyDev in eclipse is failed -

unable read repository @ http://pydev.sf.net/updates/content.xml. sun.security.validator.validatorexception: pkix path building failed: sun.security.provider.certpath.suncertpathbuilderexception: unable find valid certification path requested target i installing pydev in eclipse using python, doesn't install successfully. gives above error. happened? how solve error? i had error when trying install on laptop behind corporate firewall. couldn't tell setup of firewall, not sure of root cause. i got passed error installing pydev eclipse when out behind firewall. another way got around have eclipse on non-firewalled machine, install pydev eclipse, , copy whole eclipse directory non-firewalled machine firewalled machine. there better way, , way fix properly, didn't have time work out firewall doing. this on windows 7 machine (yeah know) not work *nix versions installed through package manager yum or apt.
Read more

PHP OOP-based login system -

lets building oop-based user authentication system, , incorporate following principles: direct injection, inheritance, encapsulation, polymorphism , single responsibility principle. my background in programming has relied on procedural programming, , thus, finding difficult put these practices correct use. assume have these classes: class config { public function set($key, $value); public function get($key, $default = null); } class user { public function __construct(pdo $dbh, $id = null); public function setprofile(profile $profile); } class auth { public function __construct(config $config); public function login($username, $password, $keeploggedin = true); public function isloggedin(); public function getloggedinuser(); public function logout(); public function register(array $data); } class session { public function start($sessionname = null); public function write($key, $value); public function read($key, $default =...
Read more

python - Exception while importing pymorphy2 -

i've installed pymorphy2 using pip: requirement satisfied (use --upgrade upgrade): pymorphy2 in /usr/local/lib/python2.7/site-packages requirement satisfied (use --upgrade upgrade): docopt>=0.6 in /usr/local/lib/python2.7/site-packages (from pymorphy2) requirement satisfied (use --upgrade upgrade): pymorphy2-dicts<3.0,>=2.4 in /usr/local/lib/python2.7/site-packages (from pymorphy2) but when i'm trying import library in python interactive shell this: >>> import pymorphy2 traceback (most recent call last): file "<stdin>", line 1, in <module> file "/usr/local/lib/python2.7/site-packages/pymorphy2/__init__.py", line 3, in <module> .analyzer import morphanalyzer file "/usr/local/lib/python2.7/site-packages/pymorphy2/analyzer.py", line 10, in <module> pymorphy2 import opencorpora_dict file "/usr/local/lib/python2.7/site-packages/pymorphy2/opencorpora_dict/__init__.py", line 4, ...
Read more