network profile
| bio | website | |
|---|---|---|
| location | ||
| age | ||
| visits | member for | 6 months |
| seen | Feb 4 at 7:09 | |
| stats | profile views | 10 |
|
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? |
|
Dec 17 |
revised |
MySQL InnoDB locks primary key on delete even in READ COMMITTED Added table schema and indexes that user had posted on a separate file-share site |
