optimize the query with multiple table

Question

I have the query in which data are coming from multiple tables.

SELECT iUserID,
       iUserID AS userID,
       (CASE vUSBG
            WHEN 1 THEN 'Yes'
            WHEN 0 THEN 'No'
        END) AS vUSBG,
       concat(vFirstname,' ',vLastname) AS Name,
       vEmail,
       eType,
       eStatus,
       tAddedDate,
       eExpert,
       eAdmin,

  (SELECT count(iUserID) AS total
   FROM tbl_friend
   WHERE iUserID = tbl_user.iUserID) AS count_f,

  (SELECT COUNT(*)
   FROM bar_followers
   WHERE bar_followers.iUserID = tbl_user.iUserID) AS bar_follows,

  (SELECT COUNT(b.iBrandID)
   FROM tbl_company_follow,
        tbl_brand b
   WHERE tbl_company_follow.iUserID = tbl_user.iUserID
     AND b.iCompanyID = tbl_company_follow.iCompanyID) AS brand_follows,

  (SELECT sum(points) AS totalpoints
   FROM tbl_points,
        tbl_post p
   WHERE iUserID = tbl_user.iUserID
     AND p.iPostID = tbl_points.post_id) AS countPoints
FROM tbl_user

This Query took 8.3595 seconds

How can i minimize the time?

sqlformat.org is your friend :)

Lukasz Szozda
– Lukasz Szozda

2015-09-21 12:00:19 +00:00
Commented Sep 21, 2015 at 12:00 — Lukasz Szozda
– Lukasz Szozda, Commented Sep 21, 2015 at 12:00
but how can i manage it in this large query?

Pathik Vejani
– Pathik Vejani

2015-09-21 12:03:27 +00:00
Commented Sep 21, 2015 at 12:03 — Pathik Vejani
– Pathik Vejani, Commented Sep 21, 2015 at 12:03
but it is taking 8.3595 sec

Pathik Vejani
– Pathik Vejani

2015-09-21 12:12:04 +00:00
Commented Sep 21, 2015 at 12:12 — Pathik Vejani
– Pathik Vejani, Commented Sep 21, 2015 at 12:12

Gordon Linoff · Accepted Answer · 2015-09-21 12:17:12Z

3

Your query is reasonable, except for the lack of explicit join syntax.

Check to be sure that you have the 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 the size of your data, 8-9 seconds might be reasonable.

answered Sep 21, 2015 at 12:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pathik Vejani Over a year ago

there are only 750 records and it is taking this much of time

Gordon Linoff Over a year ago

@bluto . . . 99.7% improvement. Not bad ;)

Pathik Vejani Over a year ago

yes.. and that is good for now.. i'll try to do it using join

O. Jones · Accepted Answer · 2015-09-21 12:17:15Z

It's hard to judge whether a sub ten second query time is reasonable without knowing your table sizes. It may near to the best you can do.

That being said, you can refactor this query to use LEFT JOIN operations in place of your dependent subqueries. That will go something like this outline.

SELECT tu.field, tu.field, ...
       a.count_f,
       b.bar_follows,
       ...
  FROM tbl_user tu
  LEFT JOIN (
              SELECT count(*) AS count_f, iUserID
                FROM tbl_friend
               GROUP BY iUserID
       ) a ON tu.iUserID = a.iUserId
  LEFT JOIN (
              SELECT count(*) AS bar_follows, iUserID
               FROM bar_followers
              GROUP BY iUserId
       ) b ON tu.iUserID = b.iUserId
 ...

There are two related things going on here. First every COUNT() item is COUNT(*). That allows the query to be satisfied without looking at the contents of each row.

Second, the dependent subqueries in your column list are replaced with subqueries working out the aggregate data you need. This may help, because the query planner can perform each subquery just once.

Gordon's right about the indexes.

Collectives™ on Stack Overflow

optimize the query with multiple table

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related