0

I am new to mySQL. I am following Mosh's tutorial to familiarize myself to SQL.

Here's my question for the following code.

SELECT *
FROM order_items
WHERE order_id = 6 AND unit_price*quantity > 30

When I looked up about SELECT *, it says: * means to return all all columns of the queried tables. Then I think SELECT * means that it grabs all tables from all schema. My question is: Isn't it a bit inefficient and confusing to return all column provided my understanding is right? If the database become bigger and bigger, it will consume unnecessary effort to look up the keyword, so I think SELECT should specify what table it is referring to. Thanks for reading!

user11838329
  • 51
  • 1
  • 7
  • All order_items columns in this case. No other tables' columns. – jarlh Oct 15 '21 at 20:23
  • Indeed, avoiding * is best practice, and not just for efficiency, but for preventing problems when columns are added/removed/reordered. Adding a column shod never affect existing queries, and results should never change based on column order – ysth Oct 15 '21 at 20:25
  • Let's say you have 5 columns and 2 of them is indexed, even your query uses indexes (means that you've filtered rows with those two indexed columns) server needs to go to table data to fetch the value of other 3 columns since you used `SELECT *`. If you would select only those two columns then server could return the values from the index without going to table data, which is much faster. – endo64 Oct 15 '21 at 20:33

1 Answers1

0

SELECT * does not fetch all tables from all schema. It only fetches the columns from the table you reference in your FROM clause. It only fetches the rows that match your WHERE clause.

The mistake is understandable given this statement in the MySQL documentation:

A select list consisting only of a single unqualified * can be used as shorthand to select all columns from all tables:

  SELECT * FROM t1 INNER JOIN t2 ...

What they mean by "all tables" is only all tables referenced in this query. And only those in FROM or JOIN clauses. Not all tables everywhere.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • @user11838329 I think it might be more helpful to read it as "It only fetches the columns from the *things* you reference in your `FROM`" - a FROM can contain things other than tables. Generally, we avoid using `*` in actual production code; it's a handy shortcut for a dev poking around a DB to find out why something isn't working but pretty soon it gets to be a nuisance – Caius Jard Oct 15 '21 at 20:30
  • Cf. [Why is using '*' to build a view bad?](https://stackoverflow.com/q/262450/20860) – Bill Karwin Oct 15 '21 at 22:00