In most SQL databases, this would be a poor way to achieve the result (and you may not be able to use HAVING without GROUP BY).
HAVING... makes no restriction on the rows joined by the queries, resulting in a large intermediate set from which the rows satisfying the HAVING clause are extracted.
Using a LEFT JOIN allows the intermediate set to be constructed using only rows that may ultimately satisfy the condition, resulting in faster execution and possibly reduced memory use.
Of course, the RDBMS may optimize your HAVING clause into a JOIN on its own, but I'd be a little troubled if that happened.
I suspect that your query used no JOIN, subquery or complex conditions; my general experience is that MySQL can pull rows out of a single table based on column value(s) pretty fast, but the more complex your query is the more MySQL sucks.
1. Because the language for a production web app shouldn't send error messages to the client (Yes PHP, I'm looking at you);
2. Because you have no choice but to perform what checks you *must* (e.g., DNS checks) in application code, but you should always enforce what integrity checks (like string length, foreign keys, etc) in the database, as well as in web forms where possible (e.g., MAXLENGTH). Otherwise, you risk a buggy component compromising your entire dataset.
3. Because whether you check your data or not, you always use placeholders to protect against SQL injection attacks.
4. You have a database mainly to ensure ACIDity so why the hell not use it, rather than cobbling together a half-assed attempt at it in your application code?
While it may not be the best design, SQL *is* the abstraction layer of choice for using an RDBMS. As written it's weird, inconsistent, potentially unparseable and arguably incomplete, but that doesn't mean it's inefficient (arguments about whether NULL values should be allowed notwithstanding).
I've written SQL for MySQL, Informix (IDS & SE), Postgresql and Oracle. Supporting all of them at once is essentially impossible (on date handling alone), but using the 'right kind of quotes', standard SQL syntax and type names for simple scalars and so on makes the differences minimal in most areas.
The remainder of the differences range from substantial missing features (stored procedures, foreign keys, triggers) which might rule a specific DBMS out for a given design, and stupid misfeatures which most databases have in some measure (I'll give Oracle a guernsey for 'select... from dual'), but which MySQL appears to make a specialty of (and appears to have little inclination to fix).
I've been in the position of taking code originally written for Informix or MySQL and having to port it to PostgresQL or Oracle, and the more standard it looked the easier it was. Why port it if it was working fine? Well, *we* had more than one customer; try telling them that they should install MySQL alongside their Oracle server...
You refer to built-in functions, different syntax, different data types etc.: SQL provides a standard library of SQL functions, and most good DBMSes allow you to define your own in SQL, C or Java, as well as 'private' languages like PlSQL and PlPgSQL; differing syntax should have no impact on how efficient a query is; and while the standard SQL types are limited, they are sufficient for a wide range of applications, and where there's a genuine need the major players have responded with very similar features (e.g., int8 and 'serial' types) that map well onto each other.
If you care, and your RDBMS supports views and stored procedures, then there's an excellent chance that you can provide a clean, efficient (within the limits of the DBMS) and consistent interface across a number of RDBMS's; it may take more time than writing a straight-to-MySQL (or whatever) app might, but it will be more portable and maintainable (and for complex apps, it may reduce your coding time in any event).
Most people here are talking about apparently deliberate misfeatures like non-standard treatment of NULL and constraints, poor date handling, expressions with non-standard results, truncation of data, etc. (to which I might add, table 'types' that are non-transpoarent in that they affect features & syntax).
As a procedural language, SQL itself is so small it's hard to see that whatever non-standard features they make available would have any impact that couldn't also be provided in a more SQL-compliant fashion.
So, can you provide an example of 'non-standard' MySQL that is better optimized than the equivalent 'standard' SQL on a decent database?
Slashdot Mirror
In most SQL databases, this would be a poor way to achieve the result (and you may not be able to use HAVING without GROUP BY).
... makes no restriction on the rows joined by the queries, resulting in a large intermediate set from which the rows satisfying the HAVING clause are extracted.
HAVING
Using a LEFT JOIN allows the intermediate set to be constructed using only rows that may ultimately satisfy the condition, resulting in faster execution and possibly reduced memory use.
Of course, the RDBMS may optimize your HAVING clause into a JOIN on its own, but I'd be a little troubled if that happened.
huiac at internode.on.net
I suspect that your query used no JOIN, subquery or complex conditions; my general experience is that MySQL can pull rows out of a single table based on column value(s) pretty fast, but the more complex your query is the more MySQL sucks.
huiac at internode.on.net
1. Because the language for a production web app shouldn't send error messages to the client (Yes PHP, I'm looking at you);
2. Because you have no choice but to perform what checks you *must* (e.g., DNS checks) in application code, but you should always enforce what integrity checks (like string length, foreign keys, etc) in the database, as well as in web forms where possible (e.g., MAXLENGTH). Otherwise, you risk a buggy component compromising your entire dataset.
3. Because whether you check your data or not, you always use placeholders to protect against SQL injection attacks.
4. You have a database mainly to ensure ACIDity so why the hell not use it, rather than cobbling together a half-assed attempt at it in your application code?
huiac at internode.on.net
Please, don't take away my stored procedures. They give me:
- Complex column & table constraints;
- Rapid retrieval of rows satisfying particular conditions, by indexing on a complex expression;
- Programmable actions on insert/update/delete (triggers)
- Updateable views
huiac at internode.on.net
I call bullshit.
... from dual'), but which MySQL appears to make a specialty of (and appears to have little inclination to fix).
While it may not be the best design, SQL *is* the abstraction layer of choice for using an RDBMS. As written it's weird, inconsistent, potentially unparseable and arguably incomplete, but that doesn't mean it's inefficient (arguments about whether NULL values should be allowed notwithstanding).
I've written SQL for MySQL, Informix (IDS & SE), Postgresql and Oracle. Supporting all of them at once is essentially impossible (on date handling alone), but using the 'right kind of quotes', standard SQL syntax and type names for simple scalars and so on makes the differences minimal in most areas.
The remainder of the differences range from substantial missing features (stored procedures, foreign keys, triggers) which might rule a specific DBMS out for a given design, and stupid misfeatures which most databases have in some measure (I'll give Oracle a guernsey for 'select
I've been in the position of taking code originally written for Informix or MySQL and having to port it to PostgresQL or Oracle, and the more standard it looked the easier it was. Why port it if it was working fine? Well, *we* had more than one customer; try telling them that they should install MySQL alongside their Oracle server...
You refer to built-in functions, different syntax, different data types etc.: SQL provides a standard library of SQL functions, and most good DBMSes allow you to define your own in SQL, C or Java, as well as 'private' languages like PlSQL and PlPgSQL; differing syntax should have no impact on how efficient a query is; and while the standard SQL types are limited, they are sufficient for a wide range of applications, and where there's a genuine need the major players have responded with very similar features (e.g., int8 and 'serial' types) that map well onto each other.
If you care, and your RDBMS supports views and stored procedures, then there's an excellent chance that you can provide a clean, efficient (within the limits of the DBMS) and consistent interface across a number of RDBMS's; it may take more time than writing a straight-to-MySQL (or whatever) app might, but it will be more portable and maintainable (and for complex apps, it may reduce your coding time in any event).
huiac at internode.on.net
Care to elaborate?
Most people here are talking about apparently deliberate misfeatures like non-standard treatment of NULL and constraints, poor date handling, expressions with non-standard results, truncation of data, etc. (to which I might add, table 'types' that are non-transpoarent in that they affect features & syntax).
As a procedural language, SQL itself is so small it's hard to see that whatever non-standard features they make available would have any impact that couldn't also be provided in a more SQL-compliant fashion.
So, can you provide an example of 'non-standard' MySQL that is better optimized than the equivalent 'standard' SQL on a decent database?
huiac at internode.on.net