August 13th, 2013, 02:17 PM
SQL code improvement and RANK()
I have a website that displays a live team score. There is a table with entries (pga_championship_2013) and a table with scores called (leaderboard_A).
I have SQL statement but when I run it it takes an age to process. Is there anyway to speed this up by improving the SQL?
I am also trying to get a RANK value created aswell similar to the Excel function RANK(). I want it to say 1,1,1,4,5,5,7 for example is there's a 3 way tie for first and a 2 way tie for 5th.
s1.to_par+s2.to_par+s3.to_par as total_score
FROM pga_championship_2013 as t
Left Join leaderboard_A as s1 On t.player1=s1.name
Left Join leaderboard_A as s2 On t.player2=s2.name
Left Join leaderboard_A as s3 On t.player3=s3.name
s1.tournament_name = 'PGA Championship' and s1.year = "2013" and
s2.tournament_name = 'PGA Championship' and s2.year = "2013" and s3.tournament_name = 'PGA Championship' and s3.year = "2013" and
s1.cut = "0" and
s2.cut = "0" and
s3.cut = "0" and
s1.wd != "1" and
s2.wd != "1" and
s3.wd != "1"
order by total_score asc
August 13th, 2013, 02:47 PM
MOD's can you please move this to the MySQL fourm I posted it in the wrong one. Thanks
August 13th, 2013, 06:32 PM
mysql does not have that functionality, so i would strongly advise to do this within your application language (php or whatever) while printing out the results
Originally Posted by crookesa
as for the performance, please do a SHOW CREATE TABLE for each table, and do an EXPLAIN on your query
by the way, your left joins will never work as left joins, just inner joins (because of the WHERE conditions) so you might as well write them as inner joins