I've tried everything I can think of, but there is still a big difference between a query run against MySQL 4.1 vs. 5.0.
Running the same single record select statement: MySQL 5.0: 78ms to 1246ms, generally in the 80ms range MySQL 4.1: 16ms to 0ms, generally in the 0ms range Even with the exact same data and my.ini. The fact the 4.1 is often 0ms leads me to think maybe it's a caching related issue, sorta, but I don't know what would explain the long ass seek time for a single record. And both MySQL instances are using the same INNODB settings, not that it matters, I've tried fresh ones, large ones, small ones... I'm not to sure where else to look to see what's happening. I can run the same query within the mysql console a hundred times and it never takes even close to a second. The only thing I can think of is some type of jdbc driver issue, but I've tried several drivers, from old to bleeding. Same results. I've tried different params in the connection string, importing the data into a fresh install, using the old data, creating indexes on primary keys... "optimizing" tables, (if that does anything for INNODB). 4.1 has done fine, so I'll just keep using it, but I was curious as to what the heck could be going on, as according to the mysql site, the innodb engine should, if anything, be faster now- not slower. I must be overlooking something, I just can't for the life of me figure out what. I think it's a clue that the 4.1 drops to 0 but the 5.0 never goes below 78ms, what that clue represents on the other hand... Hmm... another clue: I just tried selecting a single column, whereas prior I was doing a "SELECT *", and now they are both 16ms to 0ms. Well crap. As I add the columns back in one by one, it's the varchars that are slowing it down. Each one seems to add about 16ms. Now this is starting to sound like the problem that was mentioned in another thread. The plot thickens. No problem with 4.1, it's always 16-0ms, no matter the columns. Hrm. Well, it looks like the solve to the similar problem worked here as well. I'd been running the 3.1.12 driver with 4.1 with no problems, but apparently 5.0 has problems with it, because when I dropped to 3.0.17-ga, the queries are once again zippy. (3.1.10 was slow too). Strange, but looks like the same issue. Bet the other guy was running mysql 5, as I didn't see this till up'n. Well. That's odd. Eh. FWIW, didn't see this on google, so I'm still wondering if this is an edge case or something. :denny On 4/23/06, Denny Valliant <[EMAIL PROTECTED]> wrote: > > I just installed MySQL 5. I'd been meaning to for a bit. > > But is it slow compared to 4.1. It's not the server itself, as it's > whipping through queries, but CF is taking seconds now where with > 4.1 it was 16ms or so. > > I'm running INNODB. Has anyone else seen this behavior? > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:238499 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
