Unions alternatives in MySQL

Admin 1
Viewed 374

I'm running this query in mysql, but it takes quite some time to execute (0.797 sec). Which is a way too long. I believe that the root problem of my query is that im using too many UNIONS and because of that I'm loading the same tables over and over again. Is there a better implementation of this? So far I was trying to use WITH, but its not supported in MySQL, so that won't work

(
    SELECT 
        Name, Overall, Club, Wage, Nationality, `Preferred Positions`
    FROM 
        fifa_challenege.completedataset 
    WHERE 
        Nationality='Argentina'
    AND
        `Preferred Positions`='ST'
    ORDER BY 
        Overall DESC
    LIMIT 3
)
UNION
(
    SELECT 
        Name, Overall, Club, Wage, Nationality, `Preferred Positions`
    FROM 
        fifa_challenege.completedataset 
    WHERE 
        Nationality='Argentina'
    AND 
        `Preferred Positions`='CAM'
    ORDER BY 
        Overall DESC
    LIMIT 1
)
UNION
(
    SELECT 
        Name, Overall, Club, Wage, Nationality, `Preferred Positions`
    FROM 
        fifa_challenege.completedataset 
    WHERE 
        Nationality='Argentina'
    AND 
        `Preferred Positions`='LM'
    ORDER BY 
        Overall DESC
    LIMIT 1
)
UNION
(
    SELECT 
        Name, Overall, Club, Wage, Nationality, `Preferred Positions`
    FROM 
        fifa_challenege.completedataset 
    WHERE 
        Nationality='Argentina'
    AND 
        `Preferred Positions`='RM'
    ORDER BY 
        Overall DESC
    LIMIT 1
)
UNION
(
    SELECT 
        Name, Overall, Club, Wage, Nationality, `Preferred Positions`
    FROM 
        fifa_challenege.completedataset 
    WHERE 
        Nationality='Argentina'
    AND 
        `Preferred Positions`='CDM'
    ORDER BY 
        Overall DESC
    LIMIT 1
)
UNION
(
    SELECT 
        Name, Overall, Club, Wage, Nationality, `Preferred Positions`
    FROM 
        fifa_challenege.completedataset 
    WHERE 
        Nationality='Argentina'
    AND 
        `Preferred Positions`='RB'
    ORDER BY 
        Overall DESC
    LIMIT 1
)
UNION
(
    SELECT 
        Name, Overall, Club, Wage, Nationality, `Preferred Positions`
    FROM 
        fifa_challenege.completedataset 
    WHERE 
        Nationality='Argentina'
    AND 
        `Preferred Positions`='LB'
    ORDER BY 
        Overall DESC
    LIMIT 1
)
UNION
(
    SELECT 
        Name, Overall, Club, Wage, Nationality, `Preferred Positions`
    FROM 
        fifa_challenege.completedataset 
    WHERE 
        Nationality='Argentina'
    AND 
        `Preferred Positions`='CB'
    ORDER BY 
        Overall DESC
    LIMIT 2
)
UNION
(
    SELECT 
        Name, Overall, Club, Wage, Nationality, `Preferred Positions`
    FROM 
        fifa_challenege.completedataset 
    WHERE 
        Nationality='Argentina'
    AND 
        `Preferred Positions`='GK'
    ORDER BY 
        Overall DESC
    LIMIT 1
)
mysql
1 Answers

  1. add a column called rank to completedataset and pre-populate that with the ranking per Nationality, Preferred Positions and Overall. this is best achieved by another language than MySQL

  2. Create a table called Positions With Position (PRIMARY KEY) and max_rank and pre-fill that.

Like this

  CREATE TABLE Positions
  (Position CHAR(3) NOT NULL PRIMARY KEY,
  max_rank TINYINT DEFAULT 1);

Obviously Populate :

INSERT INTO Positions
  ('ST', 3),
  ('CAM', 1),
  ('LM', 1),
  ('RM', 1),
  ('DCM', 1),
  ('RB', 1),
  ('LB', 1),
  ('CB', 2),
  ('GK', 1);

Your new query 

 SELECT 
    Name, Overall, Club, Wage, Nationality, `Preferred Positions`
FROM 
    fifa_challenege.completedataset D
LEFT JOIN Positions P
   ON P.Position=D.`Preferred Positions`
WHERE 
    Nationality='Argentina'
    AND D.rank<=P.max_rank
ORDER BY 
  P.Position, 
  D.Overall DESC
user_58495600
Related