See Twitter's SQL.

http://twitter.com/dpn/statuses/851577779

by ptm • 18 years ago

49 40 18 years ago

twitter.com

bprater• 18 years ago

Mmmm... this should be juicy hacker fun for a few days!

jmorin007• 18 years ago

Days? I think we're already into negative minutes...

immad3• 18 years ago

I wonder how many minutes to techcrunching...

wallflower3• 18 years ago

Too bad we can't run 'explain plan' on them :)

ojbyrne2• 18 years ago

Explain Plan? Isn't that oracle? I ask because there is an "EXPLN" link that gives you what I think you're asking for.

wallflower3• 18 years ago

Saw that after I posted that, thanks.

newt0311• 18 years ago

postgres has something similar. Pretty nice for benchmarking and optimizing queries.

0x44• 18 years ago

In MySQL it's just "EXPLAIN <YOUR QUERY>".

ojbyrne2• 18 years ago

It seems like twitter is taking fail to new levels every day.

boredguy8• 18 years ago

I hate to upmod something so derisive, but when you have truth on your side...

amarcus2• 18 years ago

these 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

wallflower3• 18 years ago

Boy this demystifies the social animal that is twitter. The test database is very small.

sharksandwich• 18 years ago

The 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

gaius• 18 years ago

Most likely whoever wrote it cut their teeth on MySQL 4.0 or earlier which didn't have subselects.

They don't do this so much now, but back in the day (mid-late 90s) MySQL documentation was notorious for glossing over why they didn't have features. Foreign keys were "too slow". Transactions were "too slow", etc. If you need to rollback, store the previous values in memory in your own code, they told everyone (then quietly added the feature and changed the docs).

there• 18 years ago

count(*) is optimized by mysql

http://www.mysqlperformanceblog.com/2007/04/10/count-vs-coun...

jey• 18 years ago

Good, because that would be really pathetic to not perform the simplest dead code elimination.

amarcus2• 18 years ago

Only for MyISAM tables...its a different story with Innodb: http://www.mysqlperformanceblog.com/2006/12/01/count-for-inn...

fendale• 18 years ago

Mentioned elsewhere in this thread, but count() and count(1) mean basically the same thing and are equivalent.

There is a nice optimisation in MYISAM table that makes count() from table <with no where clause> almost instant as its stored as meta-data on the table - in InnoDB, Oracle or Postgres this optimisation doesn't exist ...

bdotdub• 18 years ago

Yeesh. This does not seem safe.

At least maybe (big maybe) some good will come of this?

st3fan• 18 years ago

Wouldn't a working service be more interesting than this thing? Or are they expecting us to fix their code?

IsaacSchlueter2• 18 years ago

They really weren't kidding when they said that they'd mistakenly built it as a CMS instead of as a messaging platform.

shimon• 18 years ago

The 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.

jonknee• 18 years ago

And sadly it's not even close to large-scale yet. They only have a few hundred thousand users.

IsaacSchlueter2• 18 years ago

I 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.

jamesjyu2• 18 years ago

Seems like this was a mistake by twitter: http://getsatisfaction.com/twitter/topics/staging_twitter_co...

Some profile pages are being linked to the staging server.

jamesjyu2• 18 years ago

Okay it's down now. They put up the whale page on staging and probably just disabled the plugin.

dmoney• 18 years ago

I give up. How do I enable the analyzer?

immad3• 18 years ago

I think you missed it. It seems to have disappeared.

immad3• 18 years ago

Did anyone get a screenshot before they disabled it?

Jax2• 18 years ago

Yeah... screenshot please!!! And I'm also wondering how this ink was found

socksandsandals• 18 years ago

I 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...

Jax2• 18 years ago

Before being acuired by Google, does anyone know Jaiku's uptime and other stats? Were they too this bad as Twitter?

silpol• 18 years ago

uptime 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• 18 years ago

here's a screenshot. i didn't include the table stuffs. http://skitch.com/thomasswift/xef3/twitter

dcurtis• 18 years ago

What are they using to analyze the queries like this? It looks pretty useful.

jzell• 18 years ago

They are using query reviewer

http://code.google.com/p/query-reviewer/

ashleyw• 18 years ago

Wish 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• 18 years ago

Thanks to Twitter's chief architect for giving me a good laugh :)

See Twitter's SQL.

Related Stories