100 columns long, 1/3 decimal, 1/3 text, 1/3 string. I originally stored the string as text, worked well, started using varchar(511) at some point, performance for the batch write dropped immensely. I’m no longer diving into my swap space and in fact my CPU utilization dropped very significantly too.

This post confirmed to me what I suspected was happening but don’t have the debugging chops to prove — I also couldn’t find anything on SO directly (though ultimately a comment from SO led me here).

Thanks!

Makes sense! Your first query will join the two tables, apply the WHERE, then ORDER, then LIMIT to the first row. This join might be too big for the temp tables, so it persists to disk. The second query does the sub-query first (without a JOIN) then does a simple ID match.

(id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra) VALUES
(1, ‘PRIMARY’, ‘log_request’, ‘index’, NULL, ‘PRIMARY’, ‘8’, NULL, 1, ‘Using where’),
(2, ‘DEPENDENT SUBQUERY’, ‘re’, ‘ref’, ‘FKB3DF0473B2FF1006’, ‘FKB3DF0473B2FF1006’, ‘9’, ‘func’, 1, ‘Using where; Using index’),
(2, ‘DEPENDENT SUBQUERY’, ‘r’, ‘eq_ref’, ‘PRIMARY’, ‘PRIMARY’, ‘8’, ‘func’, 1, ‘Using where; Using index’)

I found the following explanation from MySQL developers: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html and it seems the problem is in their “modified filesort algorithm”. For me it looks like a MySQL bug. Simple question: why at all MySQL operates with varchar fields of all row ignoring the fact that we need only a few rows in result? In your example you limited the result. I did the same limiting to 1 record. What stops MySQL from generation only some row ids on the first phase and asking later full number of fields specified in SELECT? They did it before optimization. So I think they have to optimize algorithm better.

And at the end the simple solution.

I changed my SQL from

SELECT
r.query, r.headers
FROM
log_request r , log_request_estimate re
WHERE
r.id = re.request_id
ORDER BY
r.id
LIMIT 1;

wrapping it with another SELECT:

SELECT body, headers FROM log_request
WHERE id IN (
SELECT r.id
FROM
log_request r , log_request_estimate re
WHERE
r.id = re.request_id and r.id<1000000
)
ORDER BY id
LIMIT 1;

I don't know exactly why does it work and I haven't yet tried it with my original request which is much more complex.
Explain command gives me the same result as for the slow request:

(id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra) VALUES
('1', 'SIMPLE', 'this_', 'index', 'FKB3DF0473B2FF1006', 'FKB3DF0473B2FF1006', '9', NULL, '76512', 'Using index; Using temporary; Using filesort'),
('1', 'SIMPLE', 'request1_', 'eq_ref', 'PRIMARY', 'PRIMARY', '8', 'petapi.this_.request_id', '1', '')

So the key takeout here is VARCHARs are represented as fixed-size in memory tables (i.e. effectively as CHARS and losing their advantage of shrinking to the data), whereas TEXT columns cause temp tables to skip memory entirely and go to disk. Both can run slow… depending on the shape of your data.

This is why most of the S.O. posts and mySQL say benchmark for your hardware and data I guess!

And another reason to avoid unnecessary select *’s

Yes, that’s a good option as well. Be sure to read the MySQL manual for times that you shouldn’t use tmpfs (eg, replication slaves).

http://dev.mysql.com/doc/refman/5.0/en/temporary-files.html

I am working on one project. We were asking users to share their stories. The file in DB against that story is varchar (512) , but the users posted stories greater than varchar (512), now client wants us to retrieve full story.
Is there any way in mysql where i can retrieve the full stories OR simple this is not possible ?

As for trying to improve the original 30ms query, the query was actually a significant part of the average page load time. The page that it was on took an average in 70ms before my change, so the query was contributing 42% of that time.

Thanks for your post! Just pondering this issue (without actually testing it) there might be another solution: Have you considered performing a smaller join operation (i.e. on t1.id and t2.id only) in a subquery first and left joining the changed VARCHAR column later?

Kind regards,
Melle