Welcome to WebmasterWorld Guest from 188.8.131.52
However, the difference in time to retrieve 1 more column is pretty nominal. I wouldn't worry about it unless you have a table with hundreds of columns or a lot of 'TEXT' type columns.
It is generally prudent to only SELECT the columns that you are going to use. Don't use 'SELECT * from table...' queries when you only are going to use one or two columns.
It depends on how the query is being executed. If MySQL is able to pull the data from an index rather than having to go to the table, then adding more columns to the query will probably force it to go to the table, making it slower.
"High Performance MySQL" goes into great depth on tuning queries and indices. It's well worth owning.
So, what if
SELECT * FROM table WHERE id=$id
must execute to retrieve the data. But you want to make sure there is at least 1 record found. Would you recommend to run that query first, then use mysql_num_rows() to check OR use COUNT(*) to check, then run that query?
Sorry, I'm new to this.
In this case there are two scenarios:
a) There are no records matching your $id.
b) there are some number of records matching your id
In (a), it doesn't matter much which method you use. To return an empty dataset is about as much time as to return a dataset with a 0 in it.
In (b), you are having to execute two queries to get all the information if you use the COUNT first, hence it takes twice the time, and more network usage to get the COUNT first.
By considering both options, it makes sense *in this case* to simply select the data, and use mysql_num_rows. However, if all you wanted to know in your application was the number of rows, then it would be more efficient to do a count()
to see if it will slow down the query, just use EXPLAIN to see how long the query takes to run:
EXPLAIN select * from table where id=$id
EXPLAIN select field1, field2 ... from table where id=$id
then you can see for yourself, the result might be different depend on settings:
1. mysql settings
2. system hardware (on a RAID0, 2CPU, the difference can be just ignored)
3. network delay (if you are connecting to remote DB, it might make sense to cache the sql result for some time)