6 ms·
See Twitter's SQL.
- bprater 18y agoMmmm... this should be juicy hacker fun for a few days!
- wallflower 18y agoToo bad we can't run 'explain plan' on them :)
- ojbyrne 18y agoExplain Plan? Isn't that oracle? I ask because there is an "EXPLN" link that gives you what I think you're asking for.
- wallflower 18y agoSaw that after I posted that, thanks.
- newt0311 18y agopostgres has something similar. Pretty nice for benchmarking and optimizing queries.
- wensing 18y agoPostgres: EXPLAIN ANALYZE.
- 0x44 18y agoIn MySQL it's just "EXPLAIN <YOUR QUERY>".
- ojbyrne 18y agoIt seems like twitter is taking fail to new levels every day.
- boredguy8 18y agoI hate to upmod something so derisive, but when you have truth on your side...
- amarcus 18y agothese queries are not optimised at all. first of all, every page gives of MySQL warnings...and second, there are alot of SELECT count(*)...instead of SELECT count(1). This may not seem like it would increase speed alot but given the number of users and the amount of times that query is executed, it will speed up twitter by a bit. There are a few other things that i have noticed...they should really clean up their sql
- wallflower 18y agoBoy this demystifies the social animal that is twitter. The test database is very small.
- sharksandwich 18y agoThe search page looks particularly sloppy. Searching 'john' yields 27 queries, including a number that look redundant Grabbing the users SELECT * FROM `users` WHERE (users.id in (<list of user ids>)) followed by queries for each user id in that list SELECT * FROM `users` WHERE (`users`.`id` = <user id>) Maybe there's a reason for doing that, but if there is, I can't think of it
- deleted 18y ago[deleted]
- senthil_rajasek 18y agoWhy roundtrip to the db for the same user info again? I have seen this type of behavior in data access "frameworks"... the optimization would be to reduce the roundtrip to the db...
- jrockway 18y agos/frameworks/poorly-written frameworks/. It's really amazing what poorly-written frameworks have done to the minds of people. When I teach classes on DBIx::Class (the Perl ORM), my students are shocked that $foo->bar->baz actually generates the SQL to follow the two relations as opposed to the "easy" "get list of results, then run query on each result, then return an array...". Actually, $foo->bar->baz, by itself, doesn't touch the database. The database is only contacted when you actually try to get information out of the result object. (And of course, the database is only queried once.)
- bdotdub 18y agoYeesh. This does not seem safe. At least maybe (big maybe) some good will come of this?
- st3fan 18y agoWouldn't a working service be more interesting than this thing? Or are they expecting us to fix their code?
- IsaacSchlueter 18y agoThey really weren't kidding when they said that they'd mistakenly built it as a CMS instead of as a messaging platform.
- shimon 18y agoThe title pretty much covers it. Twitter is more like an IM network or email list service than like a typical database-backed webapp; a relational database is just the wrong platform for large-scale messaging.
- IsaacSchlueter 18y agoI agree, of course. But before the twitter bashing starts, I wanna say, I don't think that they foresaw messaging being the primary use-case. After all, you already have IM and SMS and a gillion other messaging systems. Twitter was conceived as a blogging platform, but smaller. Messaging was how you would post to twitter, not what you would do with twitter. Of course, that was wrong. (How annoying are the twitter users who actually "answer the question: What are you doing?") But that kind of insight only comes out once your product is exposed to real humans. It's obvious, but only in hindsight. As it turns out, when you make LiveJournal really small, you get Jabber.
- jonknee 18y agoAnd sadly it's not even close to large-scale yet. They only have a few hundred thousand users.
- jamesjyu 18y agoSeems like this was a mistake by twitter: http://getsatisfaction.com/twitter/topics/staging_twitter_com?utm_medium=widget&utm_source=widget_twitter http://getsatisfaction.com/twitter/topics/staging_twitter_co... Some profile pages are being linked to the staging server.
- jamesjyu 18y agoOkay it's down now. They put up the whale page on staging and probably just disabled the plugin.
- dmoney 18y agoI give up. How do I enable the analyzer?
- immad 18y agoI think you missed it. It seems to have disappeared.
- immad 18y agoDid anyone get a screenshot before they disabled it?
- Jax 18y agoYeah... screenshot please!!! And I'm also wondering how this ink was found
- socksandsandals 18y agoI dimed this out and had it disabled. Twitter doesn't need people poking through its' SQL. Imagine what we'd find if we were poking through yours...
- nachiket56 18y agoI think you must check out the “Hello Bolo” viral on http://www.media2win.com/landingpage/Sprite/?um=10 http://www.media2win.com/landingpage/Sprite/?um=10 , its really rib-tickling!
- Jax 18y agoNachiket, That should have been "Hello Babloo"... Anyway... I didnt understand that... "Kolega to bolega". What does it mean? Open the bottle and then it will talk?
- Jax 18y agoBefore being acuired by Google, does anyone know Jaiku's uptime and other stats? Were they too this bad as Twitter?
- silpol 18y agouptime was much better, and reaction was much quicker and to the point. with recent switchover to Google infra they are almost non-stop -- only Google is not that stupid to pay for your messaging ;)
- thomasswift 18y agohere's a screenshot. i didn't include the table stuffs. http://skitch.com/thomasswift/xef3/twitter http://skitch.com/thomasswift/xef3/twitter
- ashleyw 18y agoWish I'd taken a screenshot now. It was basically a semi-transparent box in the top left of the screen which showed you the query as well as which DB served the data (for me they came from db007, and the friend data from some shards), and two links, one for a popdown box with info about the query, and another with the file trace infomation. Quite intresting, shame they have removed it.
- bluelu 18y agoThanks to Twitter's chief architect for giving me a good laugh :)