275 reputation
16
bio website
location
age
visits member for 2 years
seen Dec 1 at 9:43

Dec
12
awarded  Yearling
Jan
9
comment Amazon RDS MySQL 5.5 Innodb Lock wait timeout exceeded
Can we see the output from information_schema.innodb_trx?
Jan
8
comment T-SQL Purpose of MAX in this Group By Query
You're welcome, and I also think ypercube's was the best answer.
Jan
7
revised T-SQL Purpose of MAX in this Group By Query
missed somthing in the query
Jan
7
revised T-SQL Purpose of MAX in this Group By Query
readability
Jan
7
revised T-SQL Purpose of MAX in this Group By Query
syntax correction
Jan
7
revised T-SQL Purpose of MAX in this Group By Query
Added another alternative explanation and example of potential query alternative.
Jan
7
answered T-SQL Purpose of MAX in this Group By Query
Jan
7
comment T-SQL Purpose of MAX in this Group By Query
>>how can this return the correct results? -maybe it doesn't.
Jan
5
comment MySQL InnoDB locks primary key on delete even in READ COMMITTED
Hmmm... "two stages", a "SELECT" and then a "DELETE", SELECT "doesn't lock rows", DELETE "if check succeeds" that's exactly what was in the stored procedure that was posted here about 9 hours before this last comment. If that's the solution you accept for yourself, why not mark the one here, that was posted 9 hours before this revelation for future readers to help them realize this work-around/solution more easily. And if it's someone else's answer you mark you get 2 points to your name :-) Happy New Year.
Dec
28
comment What are the pros/cons of splitting date and time into separate fields vs. using the datetime data type and storing the date in a single field?
As far as the mention about narrower index width, you wouldn't consider that alone, but also whether dropping the unneeded time component from the index rows would perhaps greatly reduce the total number of unique index rows that need to be stored in the index. E.g., if the index is defined primarily by the timestamp data, and the 20 mil. rows are well enough spread over the time component, then eliminating the time component could reduce the number of index rows per day by a ratio of 1000's or 10's of 1000's to 1.
Dec
28
comment What are the pros/cons of splitting date and time into separate fields vs. using the datetime data type and storing the date in a single field?
One disadvantage of splitting is if anyone wants to map the same table in an ORM mapper, and if that mapper doesn't support one or both of those datatypes.
Dec
20
comment how do I limit the characters in selected row and con-cat with '…'
Assuming you indeed intend to count characters and not bytes, for character data if the character set is multi-byte such as UNICODE ucs-2, utf-8, this will produce the wrong results or cause an error. The function to always use in that case is CHAR_LENGTH() or it's synonym CHARACTER_LENGTH() cf. dev.mysql.com/doc/refman/5.0/en/…
Dec
18
comment MySQL InnoDB locks primary key on delete even in READ COMMITTED
Yeah, if FORCE INDEX doesn't work, then I don't see any other way you can try to avoid a locking table scan on the small table-(barring adding a bunch of dummy records with NULL transaction_ids, ANSI NULL settings, and your existing = operator, to fool the optimizer). But I think the work-around stored program, because it forces the locking part to come only at the end, that should minimize the impact on concurrency to a noticeable degree. Are you able to give the SP a try? (fingers crossed and all that). I hope so.
Dec
18
revised MySQL InnoDB locks primary key on delete even in READ COMMITTED
corrected link, line feeds
Dec
18
comment MySQL InnoDB locks primary key on delete even in READ COMMITTED
Added an answer with a more novel approach. Split the single delete statement into a stored procedure with an isolated read statement and isolated delete statement. The delete statement is dynamically generated using DELETE FROM proc_warnings WHERE IN (...) where the IN clause is the query result from the first statement. Still think that if there's much data, trying indexes that better cover will make a big difference, but this seems like it should keep the threads from blocking for any non-trivial time due to the mutually incompatible exclusive lock requests.
Dec
18
revised MySQL InnoDB locks primary key on delete even in READ COMMITTED
Preceeded original answer by one based on dynamic SQL
Dec
17
revised MySQL InnoDB locks primary key on delete even in READ COMMITTED
And with latest revised index suggestions, added force index hint suggestions to query rewrite.
Dec
17
revised MySQL InnoDB locks primary key on delete even in READ COMMITTED
Changed index suggestions to match MySQL syntax and in light of user's apparent current index structure
Dec
17
comment MySQL InnoDB locks primary key on delete even in READ COMMITTED
couple more questions: 1) how many rows does the day_position table normally contain, when it starts to run so slow that you have to bump the timeout limit to 500 sec? 2) How long does it take to run when you have only the sample data?