I read Baron's blog about this challenge on how to limit rows accessed, not rows returned with a query. He had another requirement, no subqueries, so it would work on pre MySQL 4.x. At first, I started down the same path he did, looking for a way to limit the number of rows accessed. The only way I could do it was to either create 4 seperte queries directly spelling out which actor_id I was looking for. I could also use an IN clause as well, but that wasn't generic enough for me. There's got to be a way to avoid a full table scan and only access a minimum number of rows to return the result he was looking for.

In researching LIMIT optimization, the MySQL 5.0 reference manual states:

If you use LIMIT row_count with …

I'll say beforehand that the following is not very clean - for a number of reasons. I'll discuss some issues after the query. The query returns all indexes in a db where the cardinality is lower than 30% of the rows, thus making it unlikely that the server will ever use that index.

SELECT s.table_name,
       concat(s.index_name,'(',group_concat(s.column_name order by s.seq_in_index),')') as idx,
       GROUP_CONCAT(s.cardinality ORDER BY s.seq_in_index) AS card,
       t.table_rows
  FROM information_schema.tables t
  JOIN information_schema.statistics s USING (table_schema,table_name)
 WHERE t.table_schema='dbname'
   AND t.table_rows > 1000
   AND s.non_unique
 GROUP BY s.table_name,s.index_name
HAVING (card + 0) < (t.table_rows / 3);

Let's discuss...
The number of rows in a table used here will be accurate for MyISAM; for InnoDB it's essentiall a rough guess.
Cardinality in this context is the number of distinct values …

Vervante Books Etc — MySQL 5.1 Cluster DBA Certification Study Guide

Only $40. Written by absolute brilliant people (and me). Needing to learn MySQL Cluster so you can go and use it? test it out? work out if it’s for you? Get this book!

Sun Microsystems Inc. today announced the immediate availability of a new offering to help customers achieve greater return on investment (ROI) and significantly reduce the costs of deploying and managing database and application server software. Sun GlassFish and MySQL Unlimited enables companies of all sizes to deploy the software on unlimited servers across their entire organization for a flat annual subscription. For more details, visit http://www.sun.com/mysql/glassfish.

Bazaar Branches of MySQL 6.0

Now that MySQL has switched to using bazaar for source code repositories, an in place to go for 6.0 public code is Canonical’s “launchpad” site, specifically “Bazaar Branches related to Sun/MySQL Engineering” at https://code.launchpad.net/~mysql. The Bazaar Branches page has a list. Each row in the list is for a source-code package with all the MySQL files necessary to build the server, as described in the MySQL Reference Manual chapter MySQL Installation Using a Source Distribution. For 6.0 lovers, the interesting entries in the Bazaar Branches list are mysql-6.0, mysql-6.0-backup, mysql-6.0-falcon, mysql-6.0-rpl, mysql-6.0-wl3220, mysql-maria.

how to read the list
The Bazaar Branches page’s instructions look good enough …

At last we are code complete with Maria 1.5, the so called crash safe version of MyISAM. Note that code complete means that all features are coded, not that the code is 100 % bug free!

The code should be available soon in a bzr repository near you. We will also shortly (as soon as we pass all build tests) make a normal source and binary release of MySQL-5.1-Maria from here.

There is still a couple of minor bugs that we know about and we will fix them shortly, but nothing that should stop you from testing/using Maria. See the file KNOWN_BUGS.txt in the source release for details.

The current release plan for MySQL-5.1-Maria is as follows:

- The current release will be an alpha release. (Technically it could have been a beta release but because we have added so much new code we wanted to signal this by making another …

You’ve probably seen the MySQL Reference Manual page “What’s New in MySQL 6.0″ and so you’ve seen this description of a new feature: “Support for extended comments for tables, columns, and indexes.” Here’s what “extended” means.

You can do a little more with COMMENTs in ALTER or CREATE statements. The easy way to see the difference is by running this statement in both MySQL 5.1 and MySQL 6.0:

SELECT table_schema, table_name, column_name, character_maximum_length

FROM information_schema.columns

WHERE column_name LIKE '%comment';

From that, you’ll see that the differences are:

TABLE_NAME     COLUMN_NAME      CHARACTER_MAXIMUM_LENGTH CHARACTER_MAXIMUM_LENGTH

                                in MySQL 5.1             in MySQL 6.0

COLUMNS        COLUMN_COMMENT   255                      1024

PARTITIONS …

It's nice to be able to make a difference.

Since David Axmark and I started to work on MySQL we also took a strong stand against software patents. MySQL AB have been sponsoring several efforts to prevent software patents in Europa.

Now David and I are continuing to do this outside of MySQL AB. We just gave an economic sponsorship to the Patent Lens, who recently lost their main sponsor, so that it can continue it's important work in making the patents system more accessible.

Here is a short description of Patent Lens from it's founder, Richard A Jefferson:


We are working to generalize the Patent Lens as the informatics platform of the Initiative for Open Innovation, which aspires to render worldwide patent systems …

I hadn’t run a race since 2003, but I have improved upon my amount of running and thought it would be good to see whether it would show in my race time. It did!

Thumbs up after a half marathon finished in record time

“From a fat bastard to a running dynamo” was what a fellow member of the MySQL Running Club SMSed me when I had proudly announced my personal record of 1:46:05 (as measured “on the safe side” by my own watch; the organisers timed me at 01:45:58) on the Münchner Stadtlauf half marathon in Munich today.

Full of energy before the race

While I may never have fully qualified for the attribute “fat”, I certainly was very bad at sports at school. My average speed …

Dear reader, this is a challenge. How’s your MySQL prowess? You know about LIMIT: it cuts off the results at the specified number.

mysql&gts; select actor_id from sakila.actor where actor_id % 5 = 0 limit 5;
+----------+
| actor_id |
+----------+
|        5 | 
|       10 | 
|       15 | 
|       20 | 
|       25 | 
+----------+
5 rows in set (0.00 sec)

But that query actually accessed 25 rows. What if I want to say “return up to 5 rows, but don’t read any more than 20 rows to find them?”

Right now I’ve got the following:

mysql> select actor_id, @rows
    -> from actor, (select @rows := 0) as x where
    ->    ((@rows := @rows + 1) <= 20)
    ->    and actor_id % 5 = 0 
    -> limit 5;
+----------+-------+
| actor_id | @rows |
+----------+-------+
|        5 | 5     | 
|       10 | 10    | 
|       15 | 15    | 
|       20 | 20    | 
+----------+-------+
4 rows in set (0.00 sec)

The …