0

I am currently using the following query to select a players most recent winning streak and the query works great other then the fact it takes way too long to load when dealing with a lot of records.

select sum(id > coalesce((select max(id) from cc6_MensLeague_rounds p2 where p2.player = 1184 and p2.outcome = 1),0)) as current from cc6_MensLeague_rounds p where player = 1184 and outcome = 2;

My question is... Is there a more efficient way to return the same results in less time?

The expected output for this query is

Current
6

I have set up a MySql fiddle at: https://www.db-fiddle.com/f/nMKRZe4EAHwqh1wfmsUbrQ/0

Yarnell Arsenault
  • 3
  • 3
  • Indexes are your friends - specifically (I would guess) a composite key on (player, outcome) – Strawberry Dec 08 '18 at 04:23
  • Thanks Strawberry, not quite sure how to go about doing that, could you please elaborate on the subject or point me to any good resources that may be useful? – Yarnell Arsenault Dec 08 '18 at 04:43
  • @YarnellArsenault Search for mysql create index and you get plenty of resources including on this site - like [this one](https://stackoverflow.com/questions/3002605/how-do-i-add-indices-to-mysql-tables) – Vasan Dec 08 '18 at 04:58

1 Answers1

0

Awesome, Thanks Strawberry, and Vason, for your insight. I added an index to the table and made a little adjustment to the select query. The results are staggering.

Here is the index query I used:

ALTER TABLE `cc6_MensLeague_rounds` ADD INDEX `outcome` (`player`,`outcome`)

Here is the modified select query:

(select sum(id > coalesce((select max(id) from cc6_MensLeague_rounds p2 where (p2.player,p2.outcome) = (cc6_MensLeague_players.id,1)),0)) as current from cc6_MensLeague_rounds p where (player,outcome) = (cc6_MensLeague_players.id,2))

My load time went from 66.21485 sec to 0.16173 sec.

Much Appreciated :-)

Yarnell Arsenault
  • 3
  • 3