CODEWITHSUNDEEP
mysqlsqldatabasequery-optimization
hello
hello

Reputation: 1229

MySQL SELECT * optimization

Is there a reason why there is enormous difference between

1. SELECT * FROM data;      -- 45000 rows
2. SELECT data.* FROM data; -- 45000 rows

SHOW PROFILES;
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.10902800 | SELECT * FROM data      |
|        2 | 0.11139200 | SELECT data.* FROM data |
+----------+------------+-------------------------+
2 rows in set, 1 warning (0.00 sec)

As far as I know it, they both return the same number of rows and columns. Why the disparity in duration?

MySQL version 5.6.29

Upvotes: 0

Views: 70

Answers (2)

Stivan
Stivan

Reputation: 1127

The particular examples specified would return the same result and have the same performance. [TableName].[column] is usually used to pinpoint the table you wish to use when two tables a present in a join or a complex statement and you want to define which column to use out of the two with the same name.

It's most common use is in a join though, for a basic statement such as the one above there is no difference and the output will be the same.

Upvotes: 1

T Gray
T Gray

Reputation: 712

That's not much difference. Neither are optimized. Both do full table scans. Both will parse to the optimizer the same. You are talking about fractions of milliseconds difference.

You can't optimize full table scans. The problem is not "select " or "select data.". The problem is that there is no "where" clause, because that's where optimization starts.

Upvotes: 2

Related Questions