"On an instance with 10 of thousands of tables, we are often flushing tables. Occasionally flush tables with read lock to take a cold snapshot for backup purposes etc, this process fails every so often:
pager grep -i flush; is invaluable in those…"
A great place to be!
Dov Endress's Page
Gifts Received
Dov Endress has not received any gifts yet
Latest Activity
Dov Endress replied to Chris's discussion BAD SQL - I've seen it happen, really!"My pleasure, this was a fun one to diagnose.
Incidentally, I made a minor tweak, calling DISTINCT against ResortID in both the outer select and sub-query as there may be two resorts with the same name and meeting the attribute…"
Dov Endress replied to Chris's discussion BAD SQL - I've seen it happen, really!"Bingo!
mysql> SELECT
-> DISTINCT(r1.Name)
-> , COUNT(r1.ResortID) as count1
-> , r1.ResortID
-> , r1.Address1
-> , r1.Address2
-> , r1.Address3
-> , r1.Address4
-> FROM
-> Resort r1
-> , ResortAttribute ra1
->…"
Dov Endress replied to Chris's discussion BAD SQL - I've seen it happen, really!"Incidentally, I should have written the HAVING clause like this:
SELECT
DISTINCT(r1.Name)
, count(r1.ResortID) AS count1
, r1.ResortID
, r1.Address1
, r1.Address2
, r1.Address3
, r1.Address4
FROM
Resort r1
, ResortAttribute ra1
WHERE
ra1.ResortID =…"
Dov Endress replied to Chris's discussion BAD SQL - I've seen it happen, really!"Nope, still not the desired result set.
This has gotten interesting...I've populated (minimally) the tables on a test server of mine:
mysql> select * from…"
Dov Endress replied to Chris's discussion BAD SQL - I've seen it happen, really!"Sarah,
Thanks, that makes sense. If then, the initial query is being run hundreds of times per minute the impact on IO would be remarkable in many cases (multiply that by thousands of tables and 'remarkable' become problematic)."
Dov Endress replied to Chris's discussion BAD SQL - I've seen it happen, really!"Hold on there, you've changed the return set criteria with that join. The last four AttributeID (I HATE camel-hump notation!) in the original query are "OR"s. Making it all one IN with a LEFT JOIN turns all of the "AND"s…"
Dov Endress left a comment for Chris"I'll make it to the UC next year for sure, if for no other reason than to heckle :P
The Oracle move is for one of 5 product lines I take care of, so I'll definitely still have MySQL around. We've been moving a design that wasn't…"
"Glad to hear you are doing well! The UC is in April next year but the call for papers is going to come out any day now. I see you are have a new role as a developer... why the switch? And, are you converting from MySQL to ORACLE for everything?…"
Dov Endress left a comment for Chris"I've been great really. Busy as hell with VCLK. We're transitioning a big chunk of data to Oracle. As for the UC, isn't it next April or something? I might be in the bay area for Oracle's Openworld in Oct, but I'm not sure.…"
Dov Endress replied to Chris's discussion BAD SQL - I've seen it happen, really!"Chris,
I imagine you and I could go back and forth all night and day with bad SQL examples...I think we have in fact.
Let's digest this one:
Someone is searching for the resort that includes all of $x amenities and may include $z amenities.…"
"glad to see you on here! what have you been up to? you coming the UC this coming year? I'm going to be presenting.
Chris"
Comment Wall (2 comments)
- At 11:05am on September 11, 2009,
Chris said…
- Glad to hear you are doing well! The UC is in April next year but the call for papers is going to come out any day now. I see you are have a new role as a developer... why the switch? And, are you converting from MySQL to ORACLE for everything? Sounds like a cool project to be on!
Let me know when you're up in the Bay, we'll do lunch!
- At 4:48pm on September 10, 2009,
- glad to see you on here! what have you been up to? you coming the UC this coming year? I'm going to be presenting.
Chris
© 2013 Created by Chris.
Powered by
