MySQL must be configured wrong, but I can't see where

Some guesses:

1 - I see in your Access string "readonly". Would that have an impact?

2 - What is the RAM space allocated to MySQL on this machine?
More generally, any idea coming from
http://dev.mysql.com/doc/refman/4.1/en/server-parameters.html
http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html ?

3 - Are your mysql tables ISAM or Innodb?

4 - with phpmyadmin or other, can you run an OPTIMIZE on the tables?

5 - Ask for an EXPLAIN of your test-query. Anything special?

6 - Are you comparing (Access results on the same machine than the Access base) with (MYSQL results collected on the same machine than MySQL) or are you comparing to (MYSQL results collected on a remote client thru a network link to MYSQL server)? In that last case, what is the volume of data transferred?

1.  I don't know why Access requires that as the ReadOnly flag seems to have no effect I can discern.

2. It was the default innodb_buffer_pool_size of 8MB, but expanding it to 1GB didn't have any impact (I noticed the linked connection was also just 8MB).

3. InnoDB.  I tried ISAM but it actually was even slower for the query I am running.

4. I didn't know myPhpAdmin could do that, I'll take a look.

5. Explain suggested an index which did help a bit, and I'm not clear why Access worked with just the obvious ones.

6. No, not the same machine.  The windows one is about three times faster taking multithreading into account and assuming 100% efficiency.  I doubt it is more than 2.5x faster in reality, which still leaves a 4x discrepancy (well, a 3x discrepancy with the larger buffer size and suggested index).

On suggestion of a website I found while researching this I tried installing a 64-bit version of Linux and that gave a boost to the point where the speeds are about the same taking into account the differences on the computers.  But it still doesn't feel right that the version of Access that comes with Office, running under 32-bit XP and completely un-tuned, should match a tuned version of MySQL under 64-bit Linux.

I'm not dissing MySQL, I'm just concerned I'm not doing something right because it goes against all common sense.  Access is not Microsoft's flagship product.

<<5. Explain suggested an index which did help a bit, and I'm not clear why Access worked with just the obvious ones.>>

Maybe there is something here: when running a query, MySQL uses just ONE index to define its optimization strategy, while Access's data engine might use several

Another desparate attempt: in MySQL are you using SELECT * or with just the same list of fields as in Access? Have you compared your MySQL SQL query with the text of Access SQL query? There would be some slight differences, but not that much.
Would you mind pasting both texts here?

A graph of the run times showed a discontinuity at 60,000 recordsets in MySQL (59,935 to be exact), so it may be a memory issue after all.  Sorry I didn't see it earlier, but it isn't obvious at all until you graph it (a jump of 6 seconds to 9 seconds) since the growth on either side of the discontinuity is identical.  64-bit OS smooths that out, which is why the performance gain is bigger on larger sets.

A little background before presenting the query.  What we have is a journal of row/column/vale sets.  Each cell has a history, and the query gets the latest values (or as of a certain date/time).  The Access and MySQL queries are identical (or rather, the changes I made to get MySQL working backport fine).

The query is in the code section below.

Unfortunately the final query needs to select that into a temp table and filter out based on (column, value) pairs, but that is the core of the issue right now.

SELECT journal.rowid, journal.column, journal.value AS last_value 
   FROM `journal` INNER JOIN 
      ( SELECT `rowid`, `column`, MAX(`date`) FROM `journal` WHERE `name` = ? GROUP BY `rowid`, `column` ) AS T
   ON journal.rowid = T.rowid AND journal.column = T.column AND journal.date = T.date
   WHERE journal.op <> 'd'
   ORDER BY journal.rowid;
                                              

1:
2:
3:
4:
5:
6:

1 - Note that when going from 32 bits to 64 bits it seems that your memory total size increases: here at EE some have reported a doubled memory need, as if the need was in "words" of 32 (or 64) instead of bytes.
If your app performance is highly important, you might want to make comparison with 32 bits

2 - Consider adding to journal a composite index, not unique, made from (rowid, column) in that order.
What does EXPLAIN say?

1. The 32 bit Linux was the initial analysis.  Switching to 64 bits was a 3x gain in speed.  I still haven't come close to using available RAM, so that shouldn't be an issue.

2, Explain suggested (rowid, column, date) which did cause a significant improvement.  As I said, with the proper index and 64 bits MySQL is now neck-in-neck with Access on XP.

I've been watching the performance tuning seminars on the MySQL website and they suggest using small queries with temporary tables over large queries.  I don't know if the query counts as large, but when I tried splitting it up it neither improved nor hurt performance until I did an inner join on two temporary tables.  It seems the query is very fast for everything up to that point (much faster than the complete query) but then a simple

SELECT temp1.rowid, `column`, `value` FROM temp1 INNER JOIN temp2 ON temp1.rowid = temp2.rowid;

takes 5 seconds, 70% of the total time.

Could that be  the core of the issue?

This query is probaly quite long if none of the temp files are indexed.
What are usually typical respective sizes for temps1 and temp2? If they are usually quite different, you might experiment the effect in the ON clause to put the smaller first, ie ON temp2.rowid=temp1.rowid  if temp2 has lots less records than temp1

Your query is currently running as a stored procedure. What is the performance impact of running the query as a normal query?

Well, temp2 is guaranteed to be smaller than temp1 because it is a subset.  It only exists because I can't use the same temporary table twice in a select statement.

I haven't tried it as a stored procedure.  Will that actually make much of a difference on a straightforward query like this?

Thanks for all this help, but since so far I've just been getting small improvements (except for converting to 64 bits) I suspect I'm not misconfigured and it's just something about the nature of the schema I am using.

I tried a stored procedure, but the time was unchanged.  I then tried moving some of the filtering into the Java caller and was pleasantly surprised.  It seems that filtering on some parts improves the speed in SQL, but a simple trade-off with Java allowed other things to filter faster there.

I'd call the speed I have now adequate.  It isn't great, but it's something I can justify to my boss.

Thanks.