Your challenge for this week is to tune a query. Say Stack Overflow has a dashboard that shows the top-ranking users in their most popular location. The Stack Overflow database even has an index to support it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE INDEX users_location_displayname ON users(location, displayname); with toplocation as ( select location from users where location <> '' group by location order by count(*) desc limit 1 ) select u.* from toplocation inner join users u on toplocation.location = u.location order by u.reputation desc limit 200; |
But the query takes several seconds to run, and the query plan has tons of estimation problems:
That’s where you come in, dear reader: can you make this query use less resources and run faster only by tuning the query?
I know, you love tuning indexes or changing server settings, but as you love to tell your developers, you can’t always throw indexes or go pulling on knobs every time there’s a query-level problem. You can make ANY changes you want to the stored procedure that you would actually implement in real life, but that’s it.
Post your solutions in the comments, and feel free to ask other folks questions about their solutions. Have fun! We’ll revisit your solutions in this post about the answers.