I am using PHP to interact with a MySQL database, and I was wondering if querying MySQL with a "SELECT * FROM..." is more or less efficient than a "SELECT id FROM...".
+4
A:
Less efficient.
If you think about it, SQL is going to send all the data for each row you select.
Imagine that you have a column called MassiveTextBlock - this column will be included when you SELECT * and so SQL will have to send all of that data over when you may not actually require it. In contrast, SELECT id is just grabbing a collection of numbers.
Graphain
2010-08-02 04:00:41
Ok, I didn't know if it sending the info was a bigger performance sucker than sorting through and choosing the right rows. Thanks.
jorgen
2010-08-02 04:02:50
@Graphain: also we should keep in mind that the people who ask such questions know nothing about performance and even SELECT id (instead of *) will not help to improve performance at indexes-less tables and with LIKE '%bla%' conditions ;-)
zerkms
2010-08-02 04:03:33
@zerkms: Not sure what that had to do with @Graphain's answer.
musicfreak
2010-08-02 04:04:22
>> than sorting through and choosing the right rows. -- what is "right"? "right" for whom? database doesn't know which columns you need in.
zerkms
2010-08-02 04:04:24
@musicfreak: that was an additional to his good answer, that newbies should not think about such "insignificant" performance "troubles" like SELECT * ;-)
zerkms
2010-08-02 04:06:25
@jorgen - When you say "sorting through and picking the rows" I assume you mean columns as in you are imagining SQL has to filter which columns to send. This is just one of those things that are so quick you're never going to notice. Treat it as though there is no performance penalty for doing this, in fact when you do `*` imagine that it pretty much looks up what all the columns are and grabs each of them anyway.
Graphain
2010-08-02 07:01:26
A:
It is less efficient because you are fetching a lot more information than just SELECT id. Additionally, the second question is much more likely to be served using just an index.
m1tk4
2010-08-02 04:02:31
A:
It would depend on why you are selecting the data. If you are writing a raw database interface like phpMySQL, then it may make sense.
If you are doing multiple queries on the same table with the same conditions and concatenation operations, then a SELECT col1, col2 FROM table may make more sense to do than using two independent SELECT col1 FROM table and a SELECT col2 FROM table to get different portions of the same data, as this performs both operations in the same query.
But, in general, you should only select the columns you need from the table in order to minimize unnecessary data from being dredged up by the DBMS. The benefits of this increase greatly if your database is on a different server from the client server, or if your database server is old and/or slow.
There is NO CONDITION in which a SELECT * is unavoidable, but if there is, then your data model probably has some serious design flaws.
amphetamachine
2010-08-02 04:09:11
A:
It depends on your indexes.
Selecting fewer columns can sometimes save a lot of time, if you select only columns that exist in the index that MySQL has used to fetch the results.
For example, if you have an index on the column id, and you perform this query:
SELECT id FROM mytable WHERE id>5
Then MySQL only needs to read the index, and does not need to even read the table row. If on the other hand, you select additional columns, as on:
SELECT id, name FROM mytable WHERE id>5
Then MySQL will need to read the id from the index, then go to the table row to read the name column.
If, however, you are reading columns that aren't in the index you're selecting on anyway, then reading more columns really won't make nearly as much difference, though it will make a small difference. If some columns contain a large amount of data, such as large TEXT or BLOB columns, then it'd be wise to exclude them if you don't need them.
thomasrutter
2010-08-02 04:13:42