Victory Road Archive

You are viewing an archive of Victory Road.

Victory Road closed on January 8, 2018. Thank you for making us a part of your lives since 2006! Please read this thread for details if you missed it.

Technology → Speeding Up MySQL (1-9-11)

Page 1 of 1

1. FreezeWarp said on January 9, 2011, 01:15:37 PM (-08:00)

Kyurem
2,186 posts

Recently I was faced with a very common challenge among MySQL, and other SQL, developers; a query was taking unnaceptably long, and without much apparent reason. After mulling this query over for no less than two hours:

Code:
SELECT user.userid, user.username, op.nickname, po.name, op.id, UNIX_TIMESTAMP(cl.timestamp) AS timestamp
FROM prefix_clicks AS cl, prefix_ownedPokemon as op, prefix_pokemon as po, user
WHERE cl.userid != 0 AND cl.pokeid = op.id AND po.id = op.pokeid AND cl.userid = user.userid
ORDER BY cl.timestamp DESC, user.username ASC
LIMIT 0,40
I came up with the following solution:
Code:
SELECT user.userid, user.username, op.nickname, po.name, op.id, UNIX_TIMESTAMP( cl.timestamp ) AS timestamp
FROM prefix_ownedPokemon AS op, prefix_pokemon AS po, (
  SELECT *
  FROM prefix_clicks
  WHERE TRUE
  ORDER BY timestamp DESC
  LIMIT 0,40
) AS cl
LEFT JOIN user ON ( cl.userid = user.userid )
WHERE op.id = cl.pokeid
AND po.id = op.pokeid
The original query took between 30 and 40 seconds, which had caused considerable server slowdown. The new one .02 seconds. Still, one main change was made, though mostly as a convenience: now it will also register empty user data for clicks (such as for guests).

Likes 1 – Cat333Pokémon

2. A'bom said on January 9, 2011, 05:30:25 PM (-08:00)

Volcarona
535 posts

I never did completely understand nested statements. I'm sure they're easy, but I never really had a good enough analogy to compare it to.

Without a data dictionary or an entity relationship diagram it's hard for me to tell what's going on there, but it kinda looks like it was the joining of the tables and the WHERE clause that made the difference. Am I right?

3. Cat333Pokémon said on January 10, 2011, 02:18:30 AM (-08:00)

Administrator
10,307 posts

Quote:
Originally Posted by A'bom View Post
I never did completely understand nested statements. I'm sure they're easy, but I never really had a good enough analogy to compare it to.
Heh, you should see the query for the chat statistics. That thing took up over 100 lines and was not fun to write!

4. A'bom said on January 10, 2011, 11:55:26 AM (-08:00)

Volcarona
535 posts

Quote:
Originally Posted by Cat333Pokémon View Post
Heh, you should see the query for the chat statistics. That thing took up over 100 lines and was not fun to write!
Holy cow, really!?

You mean just the query to generate the table showing who has how many posts where? OVER 100 lines? Seriously?
*check what the chat statistics table looks like*
*scratches head* Are you sure you're not talking about the page as a whole? Just the table itself with all of the members and their post count shouldn't take that many lines... I'm no expert, but 100 lines is an awful lot for a lone table.

5. Cat333Pokémon said on January 10, 2011, 04:15:17 PM (-08:00)

Administrator
10,307 posts

Quote:
Originally Posted by A'bom View Post
Holy cow, really!?

You mean just the query to generate the table showing who has how many posts where? OVER 100 lines? Seriously?
*check what the chat statistics table looks like*
*scratches head* Are you sure you're not talking about the page as a whole? Just the table itself with all of the members and their post count shouldn't take that many lines... I'm no expert, but 100 lines is an awful lot for a lone table.
That's because I had to select the count for every user individually by room, and then join each of the smaller tables into a larger table.

Page 1 of 1

User List - Contact - Privacy Statement - Lycanroc.Net