MySQL "Row size too large" when saving many text fields

Using MySQL? InnoDB table type? Got a table with several TEXT or BLOB fields? Getting a "Row size too large" error when saving a row with lots of text in those TEXT fields? Confused, because you thought the whole point of TEXT fields was that they stored the text off-table? Well, read on....

disclaimer: if you're storing many text fields in a relational database table, you might want to look again at whether that's the right place and method for storing that data - if it looks like a document and quacks like a document, then hey, maybe a document store would be more appropriate? But that's a whole other topic...

The detail is in the MySQL docs 14.4.5. How InnoDB Stores Variable-Length Columns , but I'll give a quick summary here. It hinges on the file format of your InnoDB engine.

As the docs say:

Early versions of InnoDB used an unnamed file format (now called Antelope) for database files. With that format, tables were defined with ROW_FORMAT=COMPACT (or ROW_FORMAT=REDUNDANT) and InnoDB stored up to the first 768 bytes of variable-length columns (such as BLOB and VARCHAR) in the index record within the B-tree node, with the remainder stored on the overflow pages.

(emphasis mine)

So with the Antelope file format, it's perfectly possible to store a single TEXT field up to 2GB without encountering the "Row size too large" error, but it's not possible to store 10 x 1k TEXT fields - because InnoDB will store the first 768 bytes of each TEXT field on the record itself, and exceed the row size limit of 8192 bytes.

The solution is straightforward (well, mostly :) - change your innodb_file_format variable to Barracuda, and alter the table to use the DYNAMIC or COMPRESSED row_format. This will store the entire contents of the TEXT fields "off-page" -

SET GLOBAL innodb_file_format=Barracuda; SET GLOBAL innodb_file_per_table=ON; ALTER TABLE (your table) ROW_FORMAT=COMPRESSED;

- and you're good to go.

The one complication is if you're running on Amazon RDS, in which case you'll get an error saying you don't have SUPER privileges. If that's the case, you just need to set the innodb_file_format parameter in your RDS instance parameter group, and allow a few seconds for it to propagate to all your instances.

Optimizing Polymorphic Association Joins On STI Models

Lets' say you have a table of assigned_users.

Users can be assigned to several different things, so you make it a polymorphic relationship:

class User < ActiveRecord::Base
  has_many :assigned_users, :as=>:user
end

class AssignedUser < ActiveRecord::Base
  belongs_to :user
  belongs_to :model, :polymorphic=>true
end

class Client < ActiveRecord::Base
  has_many :assigned_users, :as=>:model
end

class Task < ActiveRecord::Base
  has_many :assigned_users, :as=>:model
end

...and so on. At this point you'll have the following fields in your assigned_users table:

id

user_id

model_type

model_id

OK, so this assigned_users table is going to need some indexes. Like any sensible techie, you think about the most common access patterns, and create some indexes to optimize those:

add_index :assigned_users, [:user_id]

add_index :assigned_users, [:model_type, :model_id]

Great, we're good to go. 

Things tick along nicely, your assigned_users table is getting past the 'tiny' stage and into the 'medium' stage of, say, 10,000 records, but we're cool with that,  because the joins have indexes. Right?

Right! Until....

(drum roll)

....one of the parent model tables gets refactored into several sub-classes, with Single Table Inheritance.

 

(badoom.. TSH!)

What happens to your access pattern now?

Well, let's say that you refactor the Task class to have several sub-classes:

class FooTask < Task

end

class BarTask < Task

end

class BazTask < Task

end

When you now try to do a join from the base class Task onto AssignedUsers, you'll get something like this:

(I'm using Ernie Miller's Squeel syntax here, it's nice and clear)

> Task.joins{assigned_users}.to_sql

SELECT `tasks`.* FROM `tasks` INNER JOIN `assigned_users` ON `assigned_users`.`model_id` = `tasks`.`id` AND `assigned_users`.`model_type` IN ('Task', 'FooTask', 'BarTask', 'BazTask')

....and your load times go through the roof, because all of a sudden, that IN(...) clause means that your combined index on model_type and model_id can't be used, so your database resorts to a full table scan. On a 10,000-row table...?

Well, as Egon Spengler would say - "...it would be bad."

Now, we can tackle this several ways:

1. Hack ActiveRecord to generate multiple ORs on the join rather than an IN(...), or...
2. Write a scope on the parent model that constructs the join manually, using ORs, or...
3. Look for a quick and dirty solution

The first 2 sound suspiciously like rabbit holes down which we could quite easily disappear for a while... I like 3 - let's go with 3.

Luckily we can achieve big gains pretty easily, by noting that we could cut down the search space dramatically if we can just get the query optimizer to use an index on the model_id - even if every other class in your model is able to have assigned_users, the maximum number of collisions on model_id you're going to get is the number of classes you have. 

So all we need to do is add an index just on model_id -

add_index :assigned_users, [:model_id]

- and the query optimizer can now use that index in the join. Now it only needs to search through the few records with model_id = N to find those with model_type IN ('Task', 'FooTask', 'BarTask', 'BazTask'), rather than searching through every single record in the table.

Not a perfect solution, admittedly, but a pragmatic one - and one that brought down page load times by 80-90% for us. You're welcome :) 

MySQL idle connections still holding locks

We had an interesting problem today. We were seeing very slow single-row updates (>30s) on our (innodb) scheduled_jobs table, and a large number of the update queries were failing with a LOCK WAIT TIMEOUT. The updates were using the primary key, so they should be pretty fast - but they weren't.

So we fired up a console and ran SHOW INNODB STATUS, and saw lots of transactions with long-held locks -

---TRANSACTION 0 200086649, ACTIVE 3000 sec, process no 29791, OS thread id 140353331377936
12 lock struct(s), heap size 3024, 6 row lock(s), undo log entries 10
MySQL thread id 243, query id 314676 ip-10-94-245-79.ec2.internal 10.94.245.79 tn


- however, when we cross-referenced the MySQL thread IDs with a SHOW PROCESSLIST, we found that lots of the threads weren't actually doing anything:

+------+------------+-----------+-----------------+----------------+------+--------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------------+-----------+-----------------+----------------+------+--------------+------------------+
| 243 | tn | (..snip..)| tn_production | Sleep | 3000 | | NULL


This was strange - but after a bit of a Googling and a few minutes thought, we realised the cause.

When one of our worker processes dies - e.g. either because monit has killed it for taking up too much resource for too long, or because the EC2 instance it's running on has become unresponsive to ssh and been automatically restarted by our detect-and-rescue script - it may be in the middle of a transaction at the point it's killed.

If the transaction was holding locks, and never got to the "COMMIT" stage, and the connection wasn't closed normally, then that connection will persist - and maintain its locks - until it gets timed-out by the MySQL server.... and the default wait_timeout variable is 28800s - 8 hrs!.

So, we killed the idle connections, and the update queries started going through much more quickly again. We've now brought our wait_timeout variable down to 10 mins, and we'll see how we get on.

OutOfMemoryError in ActiveRecord-JDBC on INSERT SELECT

During some scale testing the other day, we came across this unusual / mildly amusing error in a database-bound command that just funnels INSERT SELECT statements down the ActiveRecord JDBC driver:

java/util/Arrays.java:2734:in `copyOf': java.lang.OutOfMemoryError: Java heap space (NativeException)
 from java/util/ArrayList.java:167:in `ensureCapacity'
 from java/util/ArrayList.java:351:in `add'
 from com/mysql/jdbc/StatementImpl.java:1863:in `getGeneratedKeysInternal'
 from com/mysql/jdbc/StatementImpl.java:1818:in `getGeneratedKeys'
 from org/apache/commons/dbcp/DelegatingStatement.java:318:in `getGeneratedKeys'
 from jdbc_adapter/JdbcAdapterInternalService.java:668:in `call'
 from jdbc_adapter/JdbcAdapterInternalService.java:241:in `withConnectionAndRetry'
 from jdbc_adapter/JdbcAdapterInternalService.java:662:in `execute_insert'
  ... 25 levels...

Now, there's a couple of things here that are worth pointing out.

1. I REALLY LOVE the fact that it blew heap space in a method called ensureCapacity. That makes me smile.
2. Why is it calling getGeneratedKeys() for an INSERT SELECT?

The getGeneratedKeys() method retrieves all the primary keys that are generated when you execute an INSERT statement. Fair enough - BUT the issue here is that we'd specifically structured the process and the SQL statements involved so as to be done with INSERT SELECTS, and hence avoid great chunks of data being transferred backwards and forwards between the app and the database.

It turns out that the ActiveRecord JDBC adapter is doing this :
(lib/active_record/connection_adapters/JdbcAdapterInternalService.java)

@JRubyMethod(name = "execute_insert", required = 1)
    public static IRubyObject execute_insert(final IRubyObject recv, final IRubyObject sql) throws SQLException {
        return withConnectionAndRetry(recv, new SQLBlock() {
            public IRubyObject call(Connection c) throws SQLException {
                Statement stmt = null;
                try {
                    stmt = c.createStatement();
                    smt.executeUpdate(rubyApi.convertToRubyString(sql).getUnicodeValue(), Statement.RETURN_GENERATED_KEYS);
                    return unmarshal_id_result(recv.getRuntime(), stmt.getGeneratedKeys());
                } finally {
                    if (null != stmt) {
                        try {
                            stmt.close();
                        } catch (Exception e) {
                        }
                    }
                }
            }
        });
    }

...in other words, explicitly telling the driver to return all the generated keys.
Hmm, OK, can we get round this by NOT calling the execute_insert method, and instead calling a raw execute method that doesn't return all the keys?

Well, no, unfortunately, because it also turns out that the ruby code is doing this:
(activerecord-jdbc-adapter-0.9/lib/active_record/connection_adapters/jdbc_adapter.rb)

      # we need to do it this way, to allow Rails stupid tests to always work
      # even if we define a new execute method. Instead of mixing in a new
      # execute, an _execute should be mixed in.
      def _execute(sql, name = nil)
        if JdbcConnection::select?(sql)
          @connection.execute_query(sql)
        elsif JdbcConnection::insert?(sql)
          @connection.execute_insert(sql)
        else
          @connection.execute_update(sql)
        end
      end

...and the JdbcConnection::insert? method is detecting if something's an insert by doing this:
(JdbcAdapterInternalService.java again)

@JRubyMethod(name = "insert?", required = 1, meta = true)
    public static IRubyObject insert_p(IRubyObject recv, IRubyObject _sql) {
        ByteList bl = rubyApi.convertToRubyString(_sql).getByteList();

        int p = bl.begin;
        int pend = p + bl.realSize;

        p = whitespace(p, pend, bl);

        if(pend - p >= 6) {
            switch(bl.bytes[p++]) {
            case 'i':
            case 'I':
                switch(bl.bytes[p++]) {
                case 'n':
                case 'N':
                    switch(bl.bytes[p++]) {
                    case 's':
                    case 'S':
                        switch(bl.bytes[p++]) {
                        case 'e':
                        case 'E':
                            switch(bl.bytes[p++]) {
                            case 'r':
                            case 'R':
                                switch(bl.bytes[p++]) {
                                case 't':
                                case 'T':
                                    return recv.getRuntime().getTrue();
                                }
                            }
                        }
                    }
                }
            }
        }
        return recv.getRuntime().getFalse();
    }

...in other words, if the sql contains the word INSERT, then it's an INSERT, and should be executed with an execute_insert call.

So, looks like we're a bit knacked here. There are two possible solutions:

1. The proper solution - fix the AR JDBC adapter (and, arguably, the MySQL connector/J as well, to stop it blowing heap space), submit a patch, wait for it to be accepted and make it into the next release.


2. Or, the pragmatic solution - rewrite the SQL-heavy command as a stored procedure and just call it with an EXECUTE and sod the DHH dogma.

We went with option 2 :)

Big kudos to D. R. MacIver for tracking down the source of the fail in double-quick time.

This should not be possible - transactional fail?

We're having a bizarre problem with MySQL 5.0.32 on Debian in a highly-concurrent environment, using the ActiveRecord JDBC adapter. We've also seen it on 5.0.51 on Ubuntu.

There's a particular sequence of 3 queries, all executed from within a ActiveRecord::Base.transaction { } block:


// Statement 1:
INSERT INTO (InnoDB table 1)
(type, ...loads of other fields...)
SELECT 'ProtoMessageItem', ...other fields...
FROM (MyISAM table 1) LEFT OUTER JOIN (InnoDB table 2)
WHERE (anti-duplication clause)
GROUP BY (synthetic identifier on MyISAM table 1, to be a unique key on InnoDB table 1)

// Statement 2:
INSERT INTO (InnoDB table 2)
SELECT (fields)
FROM (MyISAM table 1) LEFT OUTER JOIN (InnoDB table 3) INNER JOIN (InnoDB table 1)
WHERE (anti-duplication clause)

// Statement 3:
UPDATE (InnoDB table 1)
SET type = 'MessageItem'
WHERE type = 'ProtoMessageItem'


Now, all of this takes place within an explicit transaction, and works well - this approach provides huge performance gains over the more simple and obvious methods.

The problem is that just very very occasionally, it breaks. We don't know how or why, but once in a blue moon, statement 2 throws a DUPLICATE KEY exception, and the transaction DOESN'T get rolled back. Statement 1 still gets COMMITTED. We can see ProtoMessageItems in the DB, and this blocks up the pipeline and stops further MessageItems from being created.

ProtoMessageItems should NEVER be visible - they're only ever created and then updated to full MessageItems in the same 3-statement transaction. Seeing them from outside that transaction is like seeing a naked singularity - it just shouldn't be possible.

This is all kinds of wrong in a very wrong way, as it means one of the following - either:

1. The DB is getting the correct BEGIN/COMMIT/ROLLBACK statements, but transactional atomicity is not holding for some reason


2. The JDBC driver is not sending the correct ROLLBACK statements, but thinks it's in autocommit mode


3. There's an obscure concurrency edge case somewhere with the ActiveRecord JDBC adapter, that means once in a while, for some reason, it starts that transaction in autocommit mode.

All of these possibilities are worrying. We've eliminated the possibility of it being some quirk of the particular data that causes this problem. We've turned on MySQL statement logging and watched it for hours, and as far as we can see, the correct ROLLBACK statements seem to be issued every time.

Has anyone encountered a similar problem? Any known issues with INSERT/SELECT on combinations of (transactional) InnoDB and (non-transactional) MyISAM tables?

SQL QOTW - Solution!

To anyone who has been bashing their head against the desk with the SQL brain-buggerer I posted earlier, here is the solution I came up with. There may well be others, that may be more elegant, but here's the one that occurred to me...

OK, so you want to get all the documents up to a given cumulative total byte size.
A straightforward get-me-all-the-docs query looks like this:

SELECT id, filename, bytesize
FROM documents

We want to calculate the cumulative total size for all the records we've got so far, and then stop once we're about to pass the total. So the first problem is how do we work out the cumulative total?

Well, we're essentially dealing with an ordered list of records. We effectively want to work down the ordered list, calculating the cumulative total as we go.

So first lets impose an order on the list:

SELECT id, filename, bytesize
FROM documents
ORDER BY id

Now for each record, we want to work out the cumulative total. I had many thoughts on how to keep a running total, but I eventually realised that you don't actually need to. Following the old physics student's guiding principle of "if you find yourself with a problem you can't solve, rewrite it into a problem that you can solve"^**, here's the trick - we don't actually need to calculate this as "x + (next x)" - as long as we use the same imposed order, we can get this with a rather cunning subquery:

SELECT id, filename, bytesize,
       ( SELECT SUM(bytesize) 
         FROM documents d2 
         WHERE d2.id <= documents.id 
       ) AS cumulative_total
FROM documents
ORDER BY id

You see how it works? If we're working down an ordered list of ids-

1000
1001
1002
1003
1004
...
etc


- then keeping a running total is exactly equivalent to querying for the sum of all rows up-to-and-including the current row. So we can now move the subquery from the SELECT clause into the WHERE clause, and it gives us a sufficient criteria for accepting or rejecting rows:

SELECT id, filename, bytesize
FROM   documents
WHERE  ( SELECT SUM(bytesize) 
         FROM documents d2 
         WHERE d2.id <= documents.id 
       ) < @max_cumulative_size * 1024 * 1024
ORDER BY id

( where @max_cumulative_size is the maximum total size in megabytes, obviously )

It all hinges on the ordering. The one proviso with this query is that, although you are free to order the results by whatever you choose, the ordering must be the same as the implicit ordering in the subquery.

And - shock, horror, step back in amazement - that sql works as-is across MySQL and Oracle!

Yay!

So I don't know about you, but after that I feel the need to go hit the climbing wall and have a recuperative pint or two afterwards.

Have a great Christmas everyone!

---

^**This is a really useful skill to have, and it's not just applicable to Physics exams either. In fact, taken to extremes, it's perfectly possible, with a bit of cunning, to change a question about something you haven't revised, into a question about something you *have* revised....

I first realised the power of this at school, in English -
Q2.4 : Is Hamlet really mad? What is the purpose of his madness in the play and by the play? Discuss in not less than 3,000 words.

Answer : In any discussion concerning Hamlet, it is important to note that had he been Scottish, his situation would have been very similar to that of Macbeth....

:-)

SQL QOTW - SELECT all rows up to a cumulative total

Here's a little SQL test that arose here the other day. Let's say you have a DB table called documents, in which you have the following fields:

id
filename
bytesize
date_created
full_content

full_content is a BLOB, containing all the text extracted from whatever document the record represents.
bytesize is, cunningly enough, the size of the content in bytes.

You want to get all the documents, and do something to them - what you want to do is not important here, just that it involves the full_content. However, a moment of pondering will show the nasty bit of this problem... Let's say you may have tens of millions of documents, and the average bytesize is around a meg. Clearly attempting to read the full table into memory is not a good idea (at least until we get Terabytes of RAM in our servers).

A far better idea is to limit the maximum memory usage to some value, and only process up to X MB of documents in one batch. The next time round, you'll get the next X MB of docs, and so on.

We turned this problem over and over for a while, and we thought of a few ways of doing it, but all involved multiple queries, and/or things like temporary tables... and, dammitall, it SHOULD be do-able in one query, dammit man! (cue much indignant harumphing and twizzling of large mutton chop side whiskers, in a Victorian man-of-letters kind of way) Oh yeah, and here's the killer - it has to be portable across Oracle 10g and MySQL.

So the problem is this - given this table, can you construct a single query that will read all the documents up to a given maximum number of megabytes, that will run on Oracle 10g AND MySQL?

Well, after pondering this in several isolated attempts for a few days, I went back to it yesterday, and the answer just popped out straight away - and it's actually deceptively simple. I was going to just give you the answer, but I thought it might be a bit more fun to leave it as a challenge, so a big fat Brucie Bonus goes to the first person who gets it - I'll put the answer up here later on today, if no-one gets it in the meantime.

Beware Case-Insensitive Comparisons on Oracle!

Let's say you have a large database with lots of People in it. Each Person can have many EmailAddresses. Let's say that your code needs to be portable across multiple DB systems (specifically, MySQL and Oracle).

Let's also say that you process up to a million emails per day through your system, and for each email you check the sender and recipient addresses against your big DB. So that's potentially an awful lot of reads, and a potential performance bottleneck.

"No Problem!" you say, "I'll just stick an index on the EmailAddress.address field, and I'm sorted"

...but that's not quite it. EmailAddresses are case-insensitive, so that FOO@BAR.COM is the same as foo@bar.com. MySQL does a case-insensitive comparison by default, but Oracle doesn't.

So how do you get Oracle to do a case-insensitive comparison? Well, to cut a long story short, there are two parameters you can set (so long as you're on at least 10gR2):
alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=BINARY_CI;

Setting NLS_COMP to LINGUISTIC tells Oracle to perform strict case insensitivity operations, and NLS_SORT governs the sort order. The _CI suffix tells it to be case-insensitive with respect to sorting.

Lovely jubbly, smashin' sorted and great - well, actually, no. Not sorted and great at all, because there's a lovely implementation gem there - "Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer.".

So Oracle's implementation of a case-insensitive comparison is, basically, "i'm going to go through every single row in the table one-by-one, checking to see if it matches".

As Mel Smith used to say - "Aw, it's marvellous, innit?"

So what can you do? Obviously a full table scan is BAAAAD, so we need to find a way to make it use an index. There are two things you can do^**:

1) Add a explicit lower-case-address field, that duplicates the address field and explicitly lower-cases the address before writing. You can then add an index on this field, and check against that. This means you duplicate info (which is bad) and use more storage than you need (which is also bad) but it's totally portable (which is good)

2) Add a functional index to the address field:
CREATE INDEX ix_my_index_name ON email_addresses( LOWER(address) )
This means you gain the vastly superior performance of an index range scan as opposed to a full table scan, but guess what - MySQL doesn't have functional indexes yet.

GRAAAAAAAAAAAAAAAAAAAARGH!


**OK, so there is a third and possibly fourth option in this particular case, to just lower-case the address field anyway, but the point of this post was meant to be general and it does have a downside too.

You potentially lose capitalisation of the personal part of an email address. For instance:
"Alistair Davidson" <apdavidson@some-domain-im-not-going-to-tell-you.com>
would get flattened to "alistair davidson" <apdavidson@some-domain-im-not-going-to-tell-you.com>

So you want to try to keep the capitalisation if possible.

Probably the best thing to do here is actually to split the personal part from the address, and have the personal part stored as-is, with the address part lower-cased.

A Four-Way Grudge Match: Oracle vs. MySQL vs. Views vs. Derived Tables, eek..!

I like Views. SQL Views are great. Unless, that is, you have to support certainly two (MySQL and Oracle) and ideally three (plus SQLServer) of the major DB systems with the same code. Yesterday I was forced into a dastardly hack that made me wince with it's ickiness, but was actually the most practical solution given the constraints.

The Situation

We have a view which combines a LEFT OUTER JOIN with an AVG and a GROUP BY, to "collapse" what may be many related records down in to one. The AVG gets an average link weighting, to give you an effective measure of how closely two items are related by all the various different linking mechanisms.

This view references a table - item_links - that is expected to grow very rapidly, to the size of many millions of records.

We INNER JOIN onto this view in application code, to get item records related to the "current" item.

The Problem

On Oracle, this view works really well. Nice and fast, at most a tenth of a second or so per query. Doing an EXPLAIN in Oracle's SQL Developer shows that the execution plan uses all the expected indexes. Luvvly jubbly, bosh, sorted, etc.

On MySQL, however, view support is much less mature. The query optimiser can only use the underlying indexes if the view is created with CREATE ALGORITHM=MERGE and the docs say:
"The MERGE algorithm requires a one-to-one relationship between the rows in the view and the rows in the underlying table. If this relationship does not hold, a temporary table must be used instead. Lack of a one-to-one relationship occurs if the view contains any of a number of constructs:

• Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
• DISTINCT
• GROUP BY

...

... so instead, what MySQL does is select all rows into a temporary table, which is then used for executing the statement against.

Doing an EXPLAIN in MySQL shows that for the crucial operation of selecting the required rows from this potentially-huge table, there are no indexes used AT ALL. It is in fact selecting EVERY row into the temp table.

All of which leaves me - and, I must say, in blatant defiance of all known precedent - in the truly strange position of cursing MySQL whilst praising Oracle.....

Which worries me - I've never been here before.... :-)

The Solutions That Won't Work

1. Get rid of the view and just put the sql into each query,
   i.e. change INNER JOIN vw_item_links to INNER JOIN item_links ON (blah) LEFT OUTER JOIN link_prefs ON (blah) etc etc
   Hmmm.... it's possible, but I would say it's a last resort, because that would mean that we'd then have to GROUP BY every single field in every query, not just the fields in the view. This can get majorly cumbersome, because although MySQL allows you to just group by just-enough-fields-to-get-uniqueness (and thus gets a yay for ease-of-use), Oracle insists on grouping by every single field that's not an aggregate (and just about gets a begrudging yay for insisting on standards compliance)


2. Eliminate the view and the GROUP BY through using subqueries
   i.e. SELECT (item fields), (SELECT AVG(weight)  FROM item_links WHERE ...) AS weight, (SELECT other_item_id)
   This is a bit of a null option, unfortunately, as we'd still have to INNER JOIN onto item_links in the FROM clause to get the related records, so we're not really gaining anything, and in fact we'd be introducing more overhead through a correlated subquery executed for every row.

So what else can we try? Anyone? Ah yes, you boy, there at the back....

The Solution That Did Work

You can, in fact, use subqueries in the FROM clause to create DERIVED TABLES, like so:

SELECT (fields) 
FROM items 
INNER JOIN ( your view definition SQL ) inline_vw_item_links
        ON inline_vw_item_links.other_item_id = items.id
WHERE inline_vw_item_links.item_id = ...
ORDER BY inline_vw_item_links.weight DESC

in effect, putting your view SQL into the query as if it were a regular table

This does mean that you lose some of the nice encapsulation of the view, and if the view definition is complex it makes your queries look ugly, but at least it works across both DB platforms. You can also get round the ickiness problem by holding the view definition SQL in a variable :

SELECT (fields)
FROM items 
INNER JOIN ( #getViewDefinitionSql()# ) inline_vw_item_links
        ON inline_vw_item_links.other_item_id = items.id
WHERE inline_vw_item_links.item_id = ...
ORDER BY inline_vw_item_links.weight DESC

But hang on, there's one more subtlety - (and this is the icky bit)

An EXPLAIN on Oracle shows that this query still uses all the appropriate indexes, as it should. Smashin'.

But MySQL, although it's now recognising that there are indexes there to be used, is still selecting ALL rows from item_links. Which, to be fair, is what the query is *strictly* telling it to do.

So here's the icky hack:

We put the item_links WHERE clause restrictions into the inline view definition SQL, so that it looks like this:

SELECT (fields) 
FROM items 
INNER JOIN ( 
    SELECT AVG(weight) AS weight, other_item_id, (etc...)
    FROM item_links LEFT OUTER JOIN (blah) ON (whatever)
    WHERE item_links.item_id = #my_item_id# AND shared=1
 ) inline_vw_item_links
        ON inline_vw_item_links.other_item_id = items.id
WHERE inline_vw_item_links.item_id = ...
ORDER BY inline_vw_item_links.weight DESC

which you can then prettify a bit for encapsulation like this:

SELECT (fields) 
FROM items 
INNER JOIN ( 
    #getViewDefinitionSql( "WHERE item_links.item_id = #my_item_id# AND shared=1" )
 ) inline_vw_item_links
        ON inline_vw_item_links.other_item_id = items.id
WHERE inline_vw_item_links.item_id = ...
ORDER BY inline_vw_item_links.weight DESC

Now this makes me feel icky. It's (effectively) mixing your selection criteria in with your join criteria, which violates my rules for "good" sql, and goes against what I've always thought of as best practise - letting the DB engine's query optimiser work out the execution plan for itself. And it's just...wrong...

...But it works. An EXPLAIN shows that MySQL is now using the indexes that it should be using, and only selecting the rows that it needs from the potentially-many-millions-of-rows item_links tables. And the benefit is blatantly obvious in execution times, turning what was a several-second query execution time into a few tenths of a second. And Oracle is still happy. I suspect that it was previously optimising the view SQL into effectively this query anyway, under the hood. So I'm swallowing my SQL purist pomposity, and going with it. But if anyone can think of a more "proper" way of doing this, I'm all ears.