nikolaevra nikolaevra - 1 year ago 64
MySQL Question

Unions alternatives in MySQL

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
)

Answer Source
  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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download