

See Twitter's SQL. - ptm
http://twitter.com/dpn/statuses/851577779

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

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

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

~~~
shimon
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.

~~~
IsaacSchlueter
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.

------
immad
Did anyone get a screenshot before they disabled it?

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

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

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

~~~
jzell
They are using query reviewer

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

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

------
amarcus
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

~~~
sharksandwich
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
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).

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

------
dmoney
I give up. How do I enable the analyzer?

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

------
jamesjyu
Seems like this was a mistake by twitter:
[http://getsatisfaction.com/twitter/topics/staging_twitter_co...](http://getsatisfaction.com/twitter/topics/staging_twitter_com?utm_medium=widget&utm_source=widget_twitter)

Some profile pages are being linked to the staging server.

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

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

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

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

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

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

~~~
wensing
Postgres: EXPLAIN ANALYZE.

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

~~~
silpol
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 ;)

------
bdotdub
Yeesh. This does not seem safe.

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

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

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

~~~
immad
I wonder how many minutes to techcrunching...

