A common mistake I see is that people think foreign keys will automatically create indexes. Missing indexes is a general problem in SQL. Missing indexes on columns that are in foreign keys are even worse.
SQL Tips and Tricks
101–110 of 168 posts
Re: SQL Tips and Tricks
#102[dead]
Re: SQL Tips and Tricks
#103I'll add some of mine: Learn your DB server. Check the query plans often. You might get surprised. Tweak and recheck. Usually EXISTS is faster than IN. Beware that NOT EXISTS behaves differently than EXCEPT in regards to NULL values. Instead of joining tables and using distinct or similar to filter rows, consiser using subquery "columns", ie in SELECT list. This can be much faster even if you're pulling 10+ values fr…
> Learn your DB server. Check the query plans often. You might get surprised. Tweak and recheck. Oftentimes the well-designed queries behave unexpectedly, because the column statistics are not updated or when the data is fragmented for big tables (e.g. random PK insertion).
Sounds like that DBMS would work better with serial int PKs
Re: SQL Tips and Tricks
#104Earlier quoted context omitted.
I don't understand the point at all. If you need to add some condition later on, why not just add it then? What benefit is there to just marking out the spot where you might add the condition at some point in the future?
I personally don't use it too, but I think it's origins are not just readability, but from developing queries in a REPL like environment. As you develop and are constantly creating / debugging queries where you often add new and or or clauses as a whole line, that becomes much faster to add and remove those same lines as they're a single shortcut away in nearly all text editors.
Yeah, so often do I have an EXPLAIN ANALYZE query.txt file I'm repeatedly editing in one window and piping into psql in another to try and make something faster. So I put WHERE true at the top.
Re: SQL Tips and Tricks
#105Earlier quoted context omitted.
What is a dynamic, adhoc query? Why does adding 1=1 support that?
Lets say its 2001 and you are writing some hot e-commerce stuff in plain php. You want to filter data depending on multiple fields in the submitted form. If some field is there, you add one more "AND" clause to the "WHERE", like this: if (isset($_POST['product'])) { $query .= "AND product = " . $_POST['product']; }. So in order not to check every time if the added clause is the first one you start with "WHERE 1=1 ",…
I get how this isn't good. But how else would you handle multi-field filtering, keep all the ANDs and use (product_id = $1 OR $1 IS NULL) so the unset filters are no-op? That's ok as long as the query planner is smart enough.
Re: SQL Tips and Tricks
#106Re: SQL Tips and Tricks
#107Earlier quoted context omitted.
I'll add this as a caveat. I'm an analyst so my SQL isn't really exposed to anyone other than myself and so I wasn't aware of this, thanks for flagging.
A random person claims adding 1=1 is a security risk and you are going to add it as caveat without verifying if the claim is true nor knowing why? That's how misinformation spreads around. OP doesn't know what they are talking about because adding 1=1 is not a security risk. 1=1 is related to sql injections where a malicious attacker injects 'OR 1=1' into the end of the where clause to disable the where clause comple…
Read my other comments. I worked with SQL on and off since the last century. It has nothing to do with your poor assumptions.
Re: SQL Tips and Tricks
#108Earlier quoted context omitted.
You can’t control the growth rate of your tables, you can only estimate it. When we design for reliability we want to remove single cause failures and make a best effort to reduce dual cause failures. We definitely don’t want two failures from a single cause. What reason might the lack of indexes suddenly become a critical issue? And what other things might you be scrambling to deal with at the same time? Tables migh…
“And what’s the harm in making it?“ Increased storage and slower inserts?
In a table you think isn’t growing?? No.
Re: SQL Tips and Tricks
#109Earlier quoted context omitted.
You can’t control the growth rate of your tables, you can only estimate it. When we design for reliability we want to remove single cause failures and make a best effort to reduce dual cause failures. We definitely don’t want two failures from a single cause. What reason might the lack of indexes suddenly become a critical issue? And what other things might you be scrambling to deal with at the same time? Tables migh…
Depending on your application, you can very accurately estimate it. And in a case I had yesterday, it involved stringy numbers because of a third party system, so I could indeed add a computed persisted column that converts our number to a VARCHAR, add a 9th index with a lot of fields on that computed column and then save… almost nothing compared to just scanning 6k rows.
We had one such table for years. The one day I get an emergency call from support, big customer don't get their responses and it's critical for their workflow.
After some digging I found the service generating the responses got killed due to being unresponsive.
Turns out our customer got a new client which caused them to suddenly generate 100x as much data as others in this module. And that caused a lot more data in a table that joined this non-indexed table.
So everything was working, it was just the performance went over a cliff in a matter of days due to the missing index.
Added the required index and it's been humming ever since.
I've had similar experiences, and so these days I'm very liberal with indexes.
We have read-heavy workloads, if you mostly insert then sure be conservative.
Re: SQL Tips and Tricks
#110Leading comma is nice in SELECT statements because you can comment toggle individual lines. Indenting your code to make it more readable is basically what anyone with room temperature IQ does automatically. A lot of these other tips look like they're designed to deal with SQL design flaws, like how handling nulls isn't well defined in the spec so it's up to each implementation to do whatever it wants.
A lot of databases support trailing commas in select clauses. Which is just as well. I want to scratch my eyes out every time I see someone formatting with comma starting the lines. It's the kind of foolish consistency that is a big part of performative engineering.
The only ones that matter don't. If you care that much about seeing a comma somewhere you shouldn't, you have no real problems and shouldn't be complaining.