mysql - optimize the query with multiple table -

-

i have query in data coming multiple tables.

select iuserid,        iuserid userid,        (case vusbg             when 1 'yes'             when 0 'no'         end) vusbg,        concat(vfirstname,' ',vlastname) name,        vemail,        etype,        estatus,        taddeddate,        eexpert,        eadmin,    (select count(iuserid) total    tbl_friend    iuserid = tbl_user.iuserid) count_f,    (select count(*)    bar_followers    bar_followers.iuserid = tbl_user.iuserid) bar_follows,    (select count(b.ibrandid)    tbl_company_follow,         tbl_brand b    tbl_company_follow.iuserid = tbl_user.iuserid      , b.icompanyid = tbl_company_follow.icompanyid) brand_follows,    (select sum(points) totalpoints    tbl_points,         tbl_post p    iuserid = tbl_user.iuserid      , p.ipostid = tbl_points.post_id) countpoints tbl_user

this query took 8.3595 seconds

how can minimize time?

your query reasonable, except lack of explicit join syntax.

check sure have following indexes:

  • tbl_friend(iuserid)
  • bar_followers(iuserid)
  • tbl_company_follow(iuserid, icompanyid)
  • tbl_brand(icompanyid)
  • tbl_post(iuserid, ipostid)
  • tbl_points(post_id, points)

however, depending on size of data, 8-9 seconds might reasonable.


Comments

Post a Comment

Popular posts from this blog

java - Date formats difference between yyyy-MM-dd'T'HH:mm:ss and yyyy-MM-dd'T'HH:mm:ssXXX -

-
i trying parse date 2014-12-03t10:05:59.5646+08:00 using these 2 formats: yyyy-mm-dd't'hh:mm:ss yyyy-mm-dd't'hh:mm:ssxxx when parse using yyyy-mm-dd't'hh:mm:ss works fine, when parse yyyy-mm-dd't'hh:mm:ssxxx parseexception thrown. which correct format parse date , difference between these 2 formats? note : cannot use joda :( those valid formats: yyyy-mm-dd't'hh:mm:ss.sssz >>> e.g.: 2001-07-04t12:08:56.235-0700 yyyy-mm-dd't'hh:mm:ss.sssxxx >>> e.g.: 2001-07-04t12:08:56.235-07:00 edit: btw, "x" refer (iso 8601 time zone)
Read more

python - RuntimeWarning: PyOS_InputHook is not available for interactive use of PyGTK -

-
i'm using pygtk python 2.7 in ubuntu 14.04, following message: runtimewarning: pyos_inputhook not available interactive use of pygtk what reason ? when trigger? trying run script or use pygtk interactively? most likely, input hook grabbed interactive loop, e.g.: >>> import tkinter >>> root = tkinter.tk() # input hook grabbed tkinter immediate result evaluation >>> import gtk # gtk tries grab hook, fails /usr/lib/python2.7/dist-packages/gtk-2.0/gtk/__init__.py:127: runtimewarning: pyos_inputhook not available interactive use of pygtk the immediate result evaluation means expression results evaluated (e.g. window displayed) before entering main loop. have in mind, warning, not error, if bothers you, can import gtk module can (or, well, reasonably early) , release input hook: import gtk gtk.set_interactive(false) import tkinter root = tkinter.tk() # no warning here
Read more