0

I see in many examples that contains "SELECT *" statement, even if clearly not all columns, or even any data is required/wanted.

Ok, it is handy. Perhaps if someone creates an universal guide just tries to make thing simple?

But what if I do want to get just one single record from one column? Does it matter if I do want the primary key, or not - is SELECT * good practice, or just lazy/practic thing that does not matter really?

I'd give an example: In many questions/examples of "how to use "EXISTS" I see such an solution:

(...) AND EXISTS (SELECT * FROM `table` WHERE ~~STATEMENT~~)

Why should I use *, when I celarly do not need ANY data at all - all what I wanted was to check if an record that matches the STATEMENT does exist in table, nothing more. So why everywhere I see "SELECT *"? Literally, in all blogs, artcles, guides I see "SELECT *" and noone even mention about any other solution.

My guess would be, that I should select the primary key just for the best preformance, like this:

(...) AND EXISTS (SELECT `primary_key_column` FROM `table` WHERE ~~STATEMENT~~)

Am I wrong?

Zorann
  • 335
  • 2
  • 16
  • `In many questions/examples of "how to use "EXISTS" I see such an solution:` I'd be surprised if the mysql-server wouldn't optimize this itself. It _knows_ it doesn't need to select anything here. – tkausl Jan 12 '19 at 13:37
  • 1
    @tkausl: I usually perform a `EXISTS (SELECT 1/0 FROM...` to show that it doesn't matter what you write :-) – dnoeth Jan 12 '19 at 13:43
  • That would make sense, but is this confirmed information? And even if server is "smart" enough to do this - perhaps if I'd do it for him, he would'nt have to make some work to optimize it itself? – Zorann Jan 12 '19 at 13:44
  • Another dubious use of `*`: `COUNT(*)` is the usual pattern; `COUNT(col)` is not quite the same; it only counts rows where `col` is not `NULL`. – Rick James Jan 14 '19 at 00:26

1 Answers1

2

If you are writing application code, then select * is a poor practice. You want the application to be specific about the columns it is using. And you don't want to return unnecessary data to the application.

If you are writing code that you expect to run over time -- say as a scheduled job -- then select * is also not recommended for similar reasons.

MySQL tends to materialize subqueries (although it is getting better at avoiding this). So in MySQL, using select * in a subquery in the from clause (a "derived table") is not recommended for performance reasons. This is not true in other databases, which have smarter compilers.

But for daily query writing, particularly in the outermost select, select * or select t.* is a great convenience.

For EXISTS and NOT EXISTS, it makes no difference at all. I usually write:

EXISTS (SELECT 1 FROM `table` WHERE ~~STATEMENT~~)

EXISTS and NOT EXISTS are only looking for the existence of rows, not at the values in columns. So, what is selected should make no difference on performance. I use SELECT 1 because it is easy to type and I think pretty clearly conveys what it needs to.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • That does make perfect sense. Thank You very much! – Zorann Jan 12 '19 at 13:47
  • 1
    I think the duplicate link covers the question being asked. Took me 5+ minutes to find it; it's not indexed very well at all by Google. – Tim Biegeleisen Jan 12 '19 at 13:49
  • @Tim I edited my question topic - I guess that my question is more specific than the other, however I agree, that it is good to have there link to some more general data about SELECT *. – Zorann Jan 12 '19 at 13:57