5 ms·

If your query does " * " it gets all the columns in all the tables. Often, the optimizer when all the columns you need are on the index, never visits the actua

If your query does " * " it gets all the columns in all the tables. Often, the optimizer when all the columns you need are on the index, never visits the actual table (I remember the term covered index). " * " basically screws this up. "Select *" should never be used in anything in an actual production environment.

tomnipotent 5y ago
Data analysis workloads more often run into problems solved by partitioning rather than indexes.
- mistrial9 5y ago
  yes and no - I trained on "larger than RAM datasets" intentionally, and subsequently took on projects that require "larger than RAM datasets". Two things happened on my way, companies led by Google invented and deployed datasets previously impossible e.g. BigTable, and secondly the equipment I worked on went from modest to mid-sized RAM (hard to define that). Granted that lots of very useful (and common?) tasks are not "larger than RAM datasets", and then have very different characteristics.. which is starting to sound like "Excel problems look like this, they always fit in RAM, they need partitions not indexes" and the like.. There is a blind-man-and-the-Elephant drift here, which is not terrible, but might need calling out to improve..
  - tomnipotent 5y ago
    > companies led by Google invented and deployed datasets previously impossible e.g. BigTable BigTable, Redshift, Snowflake and every other "big data" storage system rely heavily on partitioning to achieve the scales they're able to achieve. Some of these systems don't even support indexes.
Hjfrf 5y ago
Covering index is right. Select * has a few valid use cases. If you're selecting from a cte or subquery then writing the same column list twice is redundant and increases complexity/risk of mistakes. * is also harmless for count and exists.
CRConrad 5y ago
I got the impression the problem is not so much the "SELECT *" as the missing "WHERE" clause.

tomnipotent 5y ago

Data analysis workloads more often run into problems solved by partitioning rather than indexes.

mistrial9 5y ago
yes and no - I trained on "larger than RAM datasets" intentionally, and subsequently took on projects that require "larger than RAM datasets". Two things happened on my way, companies led by Google invented and deployed datasets previously impossible e.g. BigTable, and secondly the equipment I worked on went from modest to mid-sized RAM (hard to define that). Granted that lots of very useful (and common?) tasks are not "larger than RAM datasets", and then have very different characteristics.. which is starting to sound like "Excel problems look like this, they always fit in RAM, they need partitions not indexes" and the like.. There is a blind-man-and-the-Elephant drift here, which is not terrible, but might need calling out to improve..
- tomnipotent 5y ago
  > companies led by Google invented and deployed datasets previously impossible e.g. BigTable BigTable, Redshift, Snowflake and every other "big data" storage system rely heavily on partitioning to achieve the scales they're able to achieve. Some of these systems don't even support indexes.

Hjfrf 5y ago

Covering index is right. Select * has a few valid use cases. If you're selecting from a cte or subquery then writing the same column list twice is redundant and increases complexity/risk of mistakes. * is also harmless for count and exists.

CRConrad 5y ago

I got the impression the problem is not so much the "SELECT *" as the missing "WHERE" clause.