Help converting multiple subselects to a single select 1

[jpadie]

I'd like some help with whether there is an easier way to achieve the stated aim without lots of sub-selects.

sql looks like this (simplified)

create table users (userID int(11), country int(11), name varchar(255));
create table countries (countryID int(11), countryName varchar(255));
create table validations (id int(11), route_id int(11));
create table routes (route_id int(11), user_id int(11), status enum('draft','filed','rejected'), dateFiled datetime);
create table logins (eventID int(11), user_id int(11), loginTime datetime);

I am looking to produce a query that outputs this

name, 
last logged in,
country name, 
number of routes that have the status 'filed'
last date on which a route was filed with the status either filed or rejected,
total number of validations for the user

I think I have been able to obtain this report via multiple subselects but the query time is in excess of 7 seconds where the route table has only 3k records, the validation 12k and the user table 2k records.

Am I missing an easy fix here?

thanks
Justin

 

[r937]

without lots of sub-selects" is possible only by removing subselects for relationships that are many-to-one for the user, e.g. countries

the other relationships, which are one-to-many for each user, must use subselects

however, you can combine two subselects into one if they both share a relationship, e.g. filedcount and lastfiled

SELECT users.name
     , ( SELECT MAX(loginTime)
           FROM logins
          WHERE user_id = users.userID ) AS last_logged_in
     , countries.countryName
     , r.filedcount
     , r.lastfiled
     , ( SELECT COUNT(*) 
           FROM routes
         INNER
           JOIN validations
             ON validations.route_id = routes.route_id
          WHERE user_id = users.userID ) AS validations
  FROM users
INNER
  JOIN countries
    ON countries.countryID = users.country
LEFT OUTER
  JOIN ( SELECT user_id
              , SUM(CASE WHEN status='filed'
                         THEN 1 
                         ELSE NULL END ) AS filedcount
              , MAX(CASE WHEN status IN ('filed','rejected')
                         THEN dateFiled 
                         ELSE NULL END ) AS lastfiled
           FROM routes
         GROUP
             BY user_id ) AS r
    ON r.user_id = users.userID

if you are having performance issues, it is not because of the number of subselects, but rather, because of inadequate indexing

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[jpadie]

Rudy
thank you. I will test this morning but my eyes are now opened to the appropriate usage differences between joins and sub-selects. I had not thought about it properly before.

nor had I considered using sub-selects as temp tables. in the past I had the sub-selects in the select part of sql, returning a single value. e.g.

select 
 u.userID,
 (select something from sometable where user_id = u.userID) as 'someField'
from usertable u

thanks
Justin