Well, i've always been able to figure out how to properly partition tables, where applicable, and usually how to ensure everything is properly normalized; and i'm usually good with indexes, but i never read all the nitty gritty details about how they work in the lower-levels until yesterday (that's great reading!). Most of it's almost common sense...
I never changed the default value of autocommit. But, as i've said before, i don't use transactions at all. Unless MySQL uses them in a way that i did not know about, transactions are never used in this database. I only use stored procedures and functions, and i do never use transactions inside them.
The reason i don't use transactions is because if something goes wrong, it will fail the procedure, and i can catch that in php and file a bug report on the error, that way i can investigate it later. If you used a transaction, then you would have to declare continue handler from exception rollback;, which doesn't return an error to the php end of your application (well, at least not without great effort). So yeah, no transactions here.....
Auto commit is ON. But the funny thing is, even if it was off, there shouldn't be anything that lasts more than 1 second. But you have me curious when you say "connections sits around for more than 50 seconds". Do you mean that innoDB locks are connection-oriented, as opposed to query-oriented? I thought that as soon as innoDB could find the appropriate records and then update/delete them, it would release the locks; but it sounds like you may be implying that the locks aren't removed until the connection that issued the query is released. Is that accurate, or did i misinterpret that? I'm going to do some deeper reading about the locks today...
"select eid into @c" works :D. It seems to have shaved a couple hundreths of a second off too. I also dropped the index from T2 which seems to have helped more. I need to find a good way to benchmark execution times, but i'm pretty satisfied this is as fast as it could reasonably be.
Thanks again for your time
premium domain names that i'm selling
Edited 1 time(s). Last edit at 06/08/2010 12:57PM by Vector Thorn.