Unions alternatives in MySQL

Question

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

)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 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
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

Unions alternatives in MySQL

Sign up