ecshreve/jepp

database query improvement

Opened this issue · 0 comments

Screenshot 2023-06-25 at 11 30 25 PM

the /api/clue?random query is the culprit behind the slowest query here.

func GetRandomClue() (*Clue, error) {

SELECT * FROM <table-name> ORDER BY RAND() LIMIT 1 works just fine for small tables, but real bad for this big (~350k row table).

the sqlx documentation helps explain

Select can save you a lot of typing, but beware! It's semantically different from Queryx, 
since it will load the entire result set into memory at once. If that set is not bounded by 
your query to some reasonable size, it might be best to use the classic Queryx/StructScan
 iteration instead.

probably going to do something like picking a random game first, and then a random clue from the game. maybe caching a chunk of random clues when the server starts could work too. haven't given it too much thought yet