Re:Why SELECT * is bad for SQL performance (2020)
by astura on Monday February 15, 2021 @08:36AM
(#26142590)
Ummm... you seem very confused... The "select" part of the query solely filters on columns, not rows. You are talking about filtering on rows.
Beyond that, when you are looking for anomalous conditions you don't say "get me all records," because how you going to pick out something that doesn't look right out of five million items? When you are looking for anomalous conditions you say "get me all records that are NOT in the form I am expecting." Then you add constraints, foreign keys, etc so it doesn't happen again.
Re:Why SELECT * is bad for SQL performance (2020)
by hnlmorg on Monday February 15, 2021 @08:15AM
(#26142404)
Unless I'm misunderstanding, what you're talking about there isn't wildcard selects but rather SELECT's without any filtering.
ie having `SELECT id, firstname, surname FROM people` would still catch 'A1234567890' and '123Smith!!!'
Where as `SELECT * from people WHERE id REGEXP '^[0-9]+$'` would exclude them despite being a wildcard SELECT.
Sometimes 'we' ask * instead of "gimme everything that starts with a number" because we may need to check that the column "ID" which is supposed to be a 10-digit number, may have an entry "A1234567890". And this has not raised any alarms. And this means that this human is not paying taxes, because his/her tax-ID is not 'run' when the gov runs the tax calculations, because the script pulls 10-digit numbers, and my ID has a letter. So some DBA got paid $50k, changed my field, run the thing, change back my field. If you think this is not happening, it is.
Consider other scenarios on obligations like that.
I once did a KYC audit for a bank. When I asked * , I laughed and cried with the results.
Soooooooooo.. yes, very click bait-y title. Each SELECT should fit the purpose it serves for the report reader. When doing KYC and you have to review 5mil clients, you want *, otherwise you will miss the: Surname: 123Smith!!!, DoB: 30/2/1980, and other duplicates, nasty surprises, people over 150yo, etc.