Reputation
13,486
Top tag
Next privilege 15,000 Rep.
Protect questions
Badges
22 50
Newest
 Guru
Impact
~796k people reached

1d
comment error in parallel query execution-single row subquery returns multiple rows
All we can tell you from getting sometimes "subquery returns multiple rows" is that a subquery sometimes returns multiple rows (where only one is expected/permitted). The question will get closed as "unclear" unless you edit it to add more detail. The query in question who be the minimum extra I'd expect to see.
Apr
29
answered No INDEX variation seems to speed up the query
Apr
25
answered MySQL Trigger: Can you turn a query updating multiple columns to multiple queries where each update one column?
Apr
25
comment How to rewrite a query with MAX in WHERE clause
I think it would be better to explain the result you want so we can work from that rather than a piece of SQL that I'm not 100% sure is getting the result you expect. It would also be useful to list the keys and indexes that are present on relevant columns on the table.
Apr
24
comment How to rewrite a query with MAX in WHERE clause
I've not gone over it in detail so I'll not post a full answer until I have, but on first sight you are filtering on the active flag of the table in the outer query in the first statement and the active flag of the table in the sub-query in the second. That would certainly give different counts for some input data.
Apr
24
revised How to rewrite a query with MAX in WHERE clause
added 199 characters in body
Apr
24
comment What does having the primary key as the last column in a composite secondary index in an InnoDB table do?
I can't find documentation to back me up (so I may be misremembering) but I'm fairly sure I've read that InnoDB doesn't keep the PK in stable order in secondary indexes unless asked to. Though the difference is minor anyway. When I next have time to play with mySQL I'll have to test the theory...
Apr
23
comment What does having the primary key as the last column in a composite secondary index in an InnoDB table do?
Assuming InnoDB works similarly in this respect to MS SQL Server, there is a difference between an index on (<some_column>) and (<some_column>, <pk>) because ON (<some_column>) is equivalent to ON (<some_column>) INCLUDE (<pk>) and not ON (<some_column>, <pk>). In most circumstances this has pretty much zero significance, but if your PK is random (i.e. a UUID) then ON (<s_c>,<pk>) can lead to extra fragmentation or if your PK is meaningful other than being a key and you might ORDER BY s_c, pk then such sorts will be faster as the index is already fully in order.
Apr
22
answered how to ignore the SSMS (sql server management studio) sql server ansi settings?
Apr
21
comment SQL Server 2008R2 - BAK file backup performance issue
How are you measuring the transfer rate? If you are reading the rate at which data is written to the backup file than compression will cause this to vary widely - at times it could be reading barely compressible data so writing as much as it is reading and at other points it could be only needing to write 10% (% figure plucked from the air!) of the amount it reads. Also is there significant contention for CPU resource? Normally these things are IO bound but if there is a lot of CPU activity from other processes the compression work the CPU is doing can sometimes be a bottleneck too.
Apr
20
answered Is it OK to use PRIMARY KEYs from one table as NAMEs of other tables?
Apr
18
awarded  Guru
Apr
16
revised How can I represent discrete ages in a relational database?
Added notes about range data types
Apr
16
answered How can I represent discrete ages in a relational database?
Apr
15
awarded  Nice Answer
Apr
15
comment MAX of a varchar column
To avoid errors you could use something like ORDER BY CASE WHEN price REGEXP '^[[:digit:]]+$' THEN price ELSE 0 END to guard against non-numeric values. But as you say, fixing the datatype is by far the best solution for this and other reasons. Ordering by a computed value like this will almost certainly force a "sort in file" if one isn't already happening so for large data it is not going to be efficient.
Apr
12
awarded  Revival
Apr
11
answered Performance of updating row if content has changed
Apr
7
comment Delete rows from a table with inner join conditions
You can do the same in MS SQL Server, and similar in postgres with its "using" clause, but IIRC Oracle does not support this. It is not in the SQL standards, hence each RBDMS that does support such things uses slightly different syntax for their extension.
Apr
5
comment Fastest way to add new column in MySQL
@RickJames: yes, you would want the renames to be atomic. I was assuming two statements wrapped in a transaction (can mySQL can handle DDL operations that way these days?). I didn't want to add more complexity to the answer though, when the tl;dr is "no, just do it the way you already are"!