mysql - How to optimize this SQL query? (Movie Database) -

-

i'm using mysql , reason takes lot of time execute queries this:

select    distinct (movies.id) id,    movies.unmoid unmoid,    movies.runtime runtime    movies inner join acted on acted.unmoid = movies.unmoid inner join actors on acted.unmoactorid = actors.unmoactorid inner join directed on directed.unmoid = movies.unmoid inner join directors on directed.unmodirectorid = directors.unmodirectorid    actors.name '%spiderman%'   or directors.name '%spiderman%'   or movies.originaltitle '%spiderman%'   or movies.englishtitle '%spiderman%'   or movies.alsoknownas '%spiderman%'   or movies.taglines '%spiderman%'   or movies.plot '%spiderman%'   , movies.validated =1   , movies.agecertificate <=20 group movies.id order added desc

this give me: "executed query in 6.5320 seconds , got 2 result(s)."

explain particular query gives:

id  select_type  table     type   possible_keys          key            key_len  ref                            rows  1   simple       movies       unmoid                 null           null     null                           925   using temporary; using filesort 1   simple       directed  ref    unmoid,unmodirectorid  unmoid         62       movies.movies.unmoid             1     1   simple       directors eq_ref unmodirectorid         unmodirectorid 62       movies.directed.unmodirectorid   1     1   simple       acted     ref    unmoid,unmoactorid     unmoid         62       movies.movies.unmoid            34    1   simple       actors    eq_ref unmoactorid            unmoactorid    62       movies.acted.unmoactorid         1   using

profiling info:

...... creating tmp table    0.000032 sorting group     0.000007 executing             0.000005 copying tmp table  6.324177 sorting result        0.000027 sending data          0.000019 ......

in worst cases takes 20 seconds execute query , every time of time goes copying tmp table. so, optimize queries , reasonable query times.

i have indexes for:

movies.id                 primary movies.unmoid             unique movies.runtime            index acted.unmoid              index acted.unmoactorid         index actors.unmoactorid        unique actors.name               uindex directed.unmoid           index directed.unmodirectorid   index directors.unmodirectorid  unique directors.name            index movies.originaltitle      index movies.englishtitle       index movies.alsoknownas        index movies.taglines           index movies.plot               index

i think reason slowness because condition wide. instead, i'd try out making simple queries , combine them union, weed out duplicates.

so, might beneficial (i can't test claim because don't have db), because assume each individual query quite fast, , there not many records merge:

(select movies.id, movies.unmoid, movies.runtime, added movies inner join actors on acted.unmoactorid = actors.unmoactorid actors.name '%spiderman%'   , movies.validated =1   , movies.agecertificate <=20) union (select movies.id, movies.unmoid, movies.runtime, added movies inner join directed on directed.unmoid = movies.unmoid inner join directors on directed.unmodirectorid = directors.unmodirectorid directors.name '%spiderman%'   , movies.validated =1   , movies.agecertificate <=20) union (select movies.id, movies.unmoid, movies.runtime, added movies  movies.originaltitle '%spiderman%'   , movies.validated =1   , movies.agecertificate <=20) union (select movies.id, movies.unmoid, movies.runtime, added movies  movies.englishtitle '%spiderman%'   , movies.validated =1   , movies.agecertificate <=20) union (select movies.id, movies.unmoid, movies.runtime, added movies  movies.alsoknownas '%spiderman%'   , movies.validated =1   , movies.agecertificate <=20) union (select movies.id, movies.unmoid, movies.runtime, added movies  movies.taglines '%spiderman%'   , movies.validated =1   , movies.agecertificate <=20) union (select movies.id, movies.unmoid, movies.runtime, added movies  movies.plot '%spiderman%'   , movies.validated =1   , movies.agecertificate <=20) order added desc

you need include fields want sort on, or filter on, in query result.

and if have engine supports subselects, can move filter conditions validation , age certification out of individual queries , common wrapper query, benefit removal of repetition, thus, maintenance:

 select id, unmoid, runtime  (select ... ) q   -- above query including necessary fields  movies.validated =1  , movies.agecertificate <=20  order added desc

Comments

Post a Comment

Popular posts from this blog

python - Scipy curvefit RuntimeError:Optimal parameters not found: Number of calls to function has reached maxfev = 1000 -

-
Image
i want make logharitmic fit. keep getting runtime error: optimal parameters not found: number of calls function has reached maxfev = 1000 i use following script. can tell me go wrong? use spyder still beginner. import math import matplotlib mpl scipy.optimize import curve_fit import numpy np #data f1=[735.0,696.0,690.0,683.0,680.0,678.0,679.0,675.0,671.0,669.0,668.0,664.0,664.0] t1=[1,90000.0,178200.0,421200.0,505800.0,592200.0,768600.0,1036800.0,1371600.0,1630800.0,1715400.0,2345400.0,2409012.0] f1n=np.array(f1) t1n=np.array(t1) plt.plot(t1,f1,'ro',label="original data") # curvefit def func(t,a,b): return a+b*np.log(t) t=np.linspace(0,3600*24*28,13) popt, pcov = curve_fit(func, t, f1n, maxfev=1000) plt.plot(t, func(t, *popt), label="fitted curve") plt.legend(loc='upper left') plt.show() your original data t1 , f1 . therefore curve_fit should given t1 second argument, not t . popt, pcov = curve_fit(func,...
Read more

binding - How can you make the color of elements of a WPF DrawingImage dynamic? -

-
we have need make vector graphic images change color of elements within graphic @ runtime. seem setting colors based on either static or dynamic resource values wouldn't work. want have multiple versions of same graphic, each abilty set graphic elements (path, line, etc) different colors don't think dynamic resource approach work. leaves data binding seems right approach. update graphic use data binding expr instead of hard-coded brush color so: <drawingimage x:key="graphic1"> <drawingimage.drawing> <drawinggroup> <drawinggroup.children> <geometrydrawing geometry="f1 m 8.4073,23.9233l"> <geometrydrawing.pen> <pen linejoin="round" brush="{binding line1brush, mode=oneway}"/> </geometrydrawing.pen> </geometrydrawing> <geometrydrawing geometry="f1 m 3.6875,2.56251l"> <geometrydrawing....
Read more

c# - How to add a new treeview at the selected node? -

-
i have treeview 4 levels; parent, child, grandchild, great-grandchild. selectednode @ grandchild level. what i'm trying create new "treeview" @ grandchild - no, dont wnat create new node "selectednode" (grandchild). should somelike this: parent child grandchild (new treeview) parent grandchild great-grandchild child great-grandchild child grandchild grandchild it similar parent table mother , father went off , had new childern different spouse other spouse of there existing children. private sub populaterootlevel() ' query find first round of parent populatenodes(dt, jcatreeview.nodes) end sub private sub populatenodes(byval dt datatable, byval nodes treenodecollection) each dr datarow in dt.rows dim tn new treenode() tn.text = dr("title").tostring()...
Read more