Sfrgttk

Deletion of copy-ctor & copy-assignment - public, private or protected?

How are passwords stolen from companies if they only store hashes?

Is there a term for accumulated dirt on the outside of your hands and feet?

Asserting that Atheism and Theism are both faith based positions

Why didn't Héctor fade away after this character died in the movie Coco?

Could Sinn Fein swing any Brexit vote in Parliament?

How can an organ that provides biological immortality be unable to regenerate?

What is the significance behind "40 days" that often appears in the Bible?

Do I need to be arrogant to get ahead?

Can a medieval gyroplane be built?

PTIJ: Do Irish Jews have "the luck of the Irish"?

What does Jesus mean regarding "Raca," and "you fool?" - is he contrasting them?

Variable completely messes up echoed string

Do US professors/group leaders only get a salary, but no group budget?

Bash - pair each line of file

Does the attack bonus from a Masterwork weapon stack with the attack bonus from Masterwork ammunition?

Worshiping one God at a time?

Help rendering a complicated sum/product formula

How do hiring committees for research positions view getting "scooped"?

Do I need to consider instance restrictions when showing a language is in P?

Would it be believable to defy demographics in a story?

Loading the leaflet Map in Lightning Web Component

Describing a chess game in a novel

What does "Four-F." mean?

Poor performance on a 'leaderboard' query

0

Consider the following 2 tables:

players:  | rooms: 

id        | id

name      | type

room_id   | embed_code_id

points    |

My query needs to fetch the 100 players with the most points from a given room type and embed_code_id.

here's what I have currently:

    select `players`.`id`,

       `players`.`points`

from `players`

       inner join `rooms` on `players`.`room_id` = `rooms`.`id`

where `rooms`.`embed_code_id` = 678

  and `rooms`.`type` = 'single'

order by `players`.`points` desc, `players`.`id` asc

limit 100;

This query results in poor performance (1.5 - 10 seconds, depends on the parameters passed)

Notes:
* Both tables have about 1M records
* when i remove the ORDER BY clause, the time drops below 100ms
* I tried setting an index on players.points & players.id, which didn't help
* I tried setting an index on embed_code_id & type which also didn't help

The EXPLAIN output suggests Using index; Using temporary; Using filesort which i don't know how to fix:

1   SIMPLE  rooms   NULL    ref PRIMARY,embed_code_id,type  type    6   const,const 478797  100.00  Using index; Using temporary; Using filesort

1   SIMPLE  players NULL    ref room_id room_id 4   playgo.rooms.id 1   100.00  NULL

Can this query be more efficient or i'm missing some index?

Thanks

mysql index join order-by

share

asked 1 min ago

Idan PtichiIdan Ptichi

31

add a comment | 

0

Consider the following 2 tables:

players:  | rooms: 

id        | id

name      | type

room_id   | embed_code_id

points    |

My query needs to fetch the 100 players with the most points from a given room type and embed_code_id.

here's what I have currently:

    select `players`.`id`,

       `players`.`points`

from `players`

       inner join `rooms` on `players`.`room_id` = `rooms`.`id`

where `rooms`.`embed_code_id` = 678

  and `rooms`.`type` = 'single'

order by `players`.`points` desc, `players`.`id` asc

limit 100;

This query results in poor performance (1.5 - 10 seconds, depends on the parameters passed)

Notes:
* Both tables have about 1M records
* when i remove the ORDER BY clause, the time drops below 100ms
* I tried setting an index on players.points & players.id, which didn't help
* I tried setting an index on embed_code_id & type which also didn't help

The EXPLAIN output suggests Using index; Using temporary; Using filesort which i don't know how to fix:

1   SIMPLE  rooms   NULL    ref PRIMARY,embed_code_id,type  type    6   const,const 478797  100.00  Using index; Using temporary; Using filesort

1   SIMPLE  players NULL    ref room_id room_id 4   playgo.rooms.id 1   100.00  NULL

Can this query be more efficient or i'm missing some index?

Thanks

mysql index join order-by

share

asked 1 min ago

Idan PtichiIdan Ptichi

31

add a comment | 

Consider the following 2 tables:

players:  | rooms: 

id        | id

name      | type

room_id   | embed_code_id

points    |

My query needs to fetch the 100 players with the most points from a given room type and embed_code_id.

here's what I have currently:

    select `players`.`id`,

       `players`.`points`

from `players`

       inner join `rooms` on `players`.`room_id` = `rooms`.`id`

where `rooms`.`embed_code_id` = 678

  and `rooms`.`type` = 'single'

order by `players`.`points` desc, `players`.`id` asc

limit 100;

This query results in poor performance (1.5 - 10 seconds, depends on the parameters passed)

Notes:
* Both tables have about 1M records
* when i remove the ORDER BY clause, the time drops below 100ms
* I tried setting an index on players.points & players.id, which didn't help
* I tried setting an index on embed_code_id & type which also didn't help

The EXPLAIN output suggests Using index; Using temporary; Using filesort which i don't know how to fix:

1   SIMPLE  rooms   NULL    ref PRIMARY,embed_code_id,type  type    6   const,const 478797  100.00  Using index; Using temporary; Using filesort

1   SIMPLE  players NULL    ref room_id room_id 4   playgo.rooms.id 1   100.00  NULL

Can this query be more efficient or i'm missing some index?

Thanks

mysql index join order-by

share

asked 1 min ago

Idan PtichiIdan Ptichi

31

players:  | rooms: 

id        | id

name      | type

room_id   | embed_code_id

points    |

    select `players`.`id`,

       `players`.`points`

from `players`

       inner join `rooms` on `players`.`room_id` = `rooms`.`id`

where `rooms`.`embed_code_id` = 678

  and `rooms`.`type` = 'single'

order by `players`.`points` desc, `players`.`id` asc

limit 100;

1   SIMPLE  rooms   NULL    ref PRIMARY,embed_code_id,type  type    6   const,const 478797  100.00  Using index; Using temporary; Using filesort

1   SIMPLE  players NULL    ref room_id room_id 4   playgo.rooms.id 1   100.00  NULL

share

asked 1 min ago

Idan PtichiIdan Ptichi

31

share

asked 1 min ago

Idan PtichiIdan Ptichi

31

asked 1 min ago

Idan PtichiIdan Ptichi

31

asked 1 min ago

Idan PtichiIdan Ptichi

31