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
)
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
add a column called
ranktocompletedatasetand pre-populate that with the ranking perNationality,Preferred PositionsandOverall. this is best achieved by another language than MySQLCreate a table called
PositionsWithPosition(PRIMARY KEY) andmax_rankand 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