I took the same table as I used for MySQL Group by Performance Tests to see how much MySQL can sort 1.000.000 rows, or rather return top 10 rows from sorted result set which is the most typical way sorting is used in practice.

I tested full table scan of the table completes in 0.22 seconds giving us about 4.5 Million of rows/sec. Obviously we can't get sorted result set faster than that.

I placed temporary sort files on tmpfs (/dev/shm) to avoid disk IO as a variable as my data set fits in memory anyway and decided to experiment with sort_buffer_size variable.

The minimum value for sort_buffer_size is 32K which gives us the following speed:

PLAIN TEXT SQL:

1. mysql> SELECT * FROM gt ORDER BY i DESC LIMIT 10;
…

In response to brad's post and daveman692's post, about Thoughts on the Social Graph.

I have posted the following:

I came home from FOOcamp with my mind buzzing with something similar. It was in the zeitgeist, I guess.

Between being annoyed at some of 6As recent actions, and annoyed that if I left LJ, I would lose a lot of valuable social network information. And then the sessions on distributed social networks, openid, …

So, like I mentioned in the Log Buffer #58 this afternoon, the MySQL blog world was abuzz during these last two weeks — as were various IRC channels — with debates and discussions about the latest news Kaj blogged about with regards to further efforts by MySQL to differentiate.

At the risk of sounding like a piece of milk toast, I would like to emphasize the point that the collective "we" — MySQL AB, the MySQL user community, and MySQL paying customers — are all in this thing together. If one piece of the collective is hurt, it affects all the others in both direct and indirect ways.

The collective "we" is currently amidst an evolutionary process: the path of …

MySQL has a unique feature whereby you can select the storage engine for each table. If you don’t know what a storage engine is, it is responsible for managing the physical data on disk - from data files and indexes to the memory buffers used to access the data and the locking needed to prevent multiple people from clobbering each other’s changes. This is both a boon and a bane for Systems Administrators and DBAs.

It’s a great feature because developers, Sys Admins and DBAs can select the engine with the properties they require. For example, the InnoDB engine supports ACID-compliant transactions, foreign keys and row-level locking. The MyISAM engine, while being lightning fast on reads and supporting concurrent inserts, does not have ACID-compliant transactions or foreign keys and implements table-level locking. Other engines are available, such as memory-based tables, clustered tables, federated tables (reference a table on server B …

Jan has a good article about finding the row matching some value in the group:

This is one illustration of group by limitations in SQL language which is not offset by any MySQL specific extensions,yet
As you can see if you want to get one row from the group which is sorted some way you have to use ugly hacks. This is because SQL does not have a notion of sorting the data within the groups and in fact ANSI SQL even forbids you to select columns which are not aggregates or part of group by because result in this case is not defined.

What would be quite helpful is to have GROUPORDER (pick the name) clause which defines which element is selected for non aggregate columns, something like

PLAIN TEXT SQL:

1. SELECT MAX(Population),  City, Country  FROM City GROUP BY Country GROUPORDER …

The 58th edition of Log Buffer, the weekly review of database blogs, has been published by Jay Pipes (a.k.a J.Pipes) of MySQL AB on Design, Develop, Discover, Define. Chen Shapira is standing by for the next edition. Publishing a Log Buffer on your own blog is a great way to introduce yourself and your blog [...]

Dave Edwards once again has given me the privilege of writing this week's Log Buffer, and oooooh it's a jam-packed one. I think there's something for everyone in this week's issue.

Buzz in the MySQL Blogosphere...

The MySQL blogosphere was alight this past week — and not just with technical articles. The recent announcement by Kaj Arnö that MySQL was making some adjustments to its Enterprise and Community Server release schedules and removing source tarballs for the Enterprise Server from its public FTP site made waves in a number of circles. On PlanetMySQL, bloggers such as Jonathan Cheyer, Jeremy Cole, Kevin …

Bug: 30485

[miguel@skybr 4.1]$ bin/mysql -uroot db77
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.24-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `GiftCodes` (
    ->   `code` varchar(32) collate utf8_bin NOT NULL default '',
    ->   `used_by_id` bigint(10) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`code`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> insert into GiftCodes values ('foo',7);
Query OK, 1 row affected (0.00 sec)

mysql> select * from GiftCodes where code='foo';
+------+------------+
| code | used_by_id |
+------+------------+
| foo  |          7 …

Sourcefire has bought the ClamAV project for an extraordinary, one-time charge in the third quarter of 2007 of between 9¢ and 12¢ per Sourcefire share. ClamAV is an open source project that has created an anti virus engine, packages that support its use on Linux, Unix, BSD and Win32, and a signature database that currently contains more than 147,000 signatures. It is by most estimates the most commonly used open source anti virus product in the world.

The deal covers the intellectual property, copyrights, logo, domain names, Sourceforge and Freshmeat areas, naming rights and other rights to Clam, the Open source anti virus engine and signature database. The ClamAV team - in particular Tomasz Kojm, will become Sourcefire employees and continue their management of the project on a day-to-day basis.

Do you happen to have functions that read all rows of a database result into one array, without processing the rows, and pass the array to other functions? For example, do you fetch results in your database class and pass them to a template engine displaying? If so, here is excellent news for you. mysqli_fetch_all(), which comes with mysqlnd, does the task of fetching the data sometimes twice as fast as mysqli_fetch_array(). Reason being: it saves a loop with function calls…

mixed mysqli_fetch_all( mysqli_result $result [, int $resulttype] )

The new API call, which is only available if you build ext/mysqli with mysqlnd support, does fetch all rows of a result set into an array with just one function call:

$all_rows = mysqli_fetch_all($res);

The one mysqli_fetch_all() call does exactly the same as the following loop: