3 ms·
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 cou
by amarcus 18y 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
- 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.)
- gaius 18y agoMost 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).
- deleted 18y ago[deleted]
- there 18y agocount(*) is optimized by mysql http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/ http://www.mysqlperformanceblog.com/2007/04/10/count-vs-coun...
- jey 18y agoGood, because that would be really pathetic to not perform the simplest dead code elimination.
- amarcus 18y agoOnly for MyISAM tables...its a different story with Innodb: http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/ http://www.mysqlperformanceblog.com/2006/12/01/count-for-inn...
- neilc 18y agoNot AFAICS; count( * ) and count( 1 ) are trivially equivalent, and this doesn't depend on the storage engine being used. It is true that count( * ) without a WHERE clause is much faster in MyISAM than in Innodb (or Postgres), but that is a different optimization (count( * ) => metadata lookup, not count( 1 ) => count( * )).
- fendale 18y agoMentioned 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 ...