Hey there. My company writes a lot of social applications, and there is one operation that is very common, but I don't know if MySQL supports it in a good way. I thought I'd write to this list for two reasons:
1) Maybe MySQL has a good way to do this, and I just don't know about it
2) Propose to MySQL developers a simple algorithm which would greatly improve MySQL support for social networking apps.
Here is the situation. Let's say I have built a social networking application where people create and edit some item (article, photo, music mix, whatever). Now, a typical user logs in, and this user has 3000 friends. How can I quickly find all the articles written by this user's friends, and not just random articles?
Ideally, I would want to write something like this:
SELECT * FROM article WHERE user_id IN (345789, 324875, 398, ..., 349580)
basically, execute a query with a huge IN ( ... ). Maybe if this would exceed the buffer size for the MySQL wire protocol, I would break up the list into several lists, and execute several queries, and union the results together myself.
But my point is, this is very common for social networking apps. Every app wants to show "the X created by your friends", or "friends of yours (given some list from a social network) who have taken action X".
Here is how I would do it if I had raw access to the MySQL index in memory:
a) Sort the list of entries in the IN, in ascending order.
b) Do *ONE* binary search through the index (assuming it's a BTREE index) and get them all in one pass. If it's a HASH index or something, I would have to look up each one individually.
The benefits of this approach would be that this common operation would be done extremely quickly. If the index fits entirely in memory, and I just want to get the primary keys (i.e. get the list of friends who did X), the disk isn't even touched. In addition, for BTREE indexes, I would just need ONE binary search, because the entries have been sorted in ascending order.
Does MySQL have something like this? And if not, perhaps you can add it in the next version? It would really boost MySQL's support for social networking apps tremendously. Alternative, how can I add this to my MySQL? Any advice would be appreciated.
Sincerely, Gregory Magarshak Qbix
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=listsearcharchive@lassosoft.com
Mar 29
Peter Brawley Re: A common request
Mar 29, 2011; 13:10
Peter Brawley
Re: A common request
Mar 29
Gregory Magarshak Re: A common request
Mar 29, 2011; 14:27
Gregory Magarshak
Re: A common request
Mar 29
Peter Brawley Re: A common request
Mar 29, 2011; 15:17
Peter Brawley
Re: A common request
Mar 29
Sander de Bruijne Re: A common request
Mar 29, 2011; 22:53
Sander de Bruijne
Re: A common request
Mar 31
Gregory Magarshak Re: A common request
Mar 31, 2011; 08:29
Gregory Magarshak
Re: A common request
Mar 31
Gregory Magarshak Re: A common request
Mar 31, 2011; 08:32
Gregory Magarshak
Re: A common request
Mar 31
Johan De Meersman Re: A common request
Mar 31, 2011; 18:06
Johan De Meersman
Re: A common request
Mar 31
mos Re: A common request
Mar 31, 2011; 11:20
mos
Re: A common request
Mar 31
Johan De Meersman Re: A common request
Mar 31, 2011; 20:20
Johan De Meersman
Re: A common request
Mar 31
mos Re: A common request
Mar 31, 2011; 14:33
mos
Re: A common request
Mar 31
Wm Mussatto Re: A common request
Mar 31, 2011; 13:16
Wm Mussatto
Re: A common request
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!