mysql query speed

"SELECT field1" VS "SELECT field1, field2 ..."

[squallions]

Is it true that the more fields to select in the SELECT query, it will slow down the query?

[s1dev]

In theory, yes.

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.

[squallions]

how about mysql_num_rows() compare to COUNT(*)?

[s1dev]

If you just want to know how many, use count(*). It is faster.

[s1dev]

select count(*) - returns a single record. Not much traffic between the database server and the webserver.
select [fieldlist] - returns all the records, from which you can get the count, but with much more traffic between your servers.

[squallions]

Thank.

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.

[coopster]

Maybe this discussion will help shed some light...

[webmasterworld.com...]

[squallions]

Thank.

[SeanW]

It's one of those "it depends" things.

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.

Sean

[ggrot]

Thank.

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()

[squallions]

Great. Thank.

[listerine]

not all storage engine in mysql support count(*), for example, InnoDB does not support this function (but it comes with row level locking as compare to the default MyISAM's table level locking).

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)

[Xuefer]

"not all storage engine in mysql support count(*)"
read: "not all storage engine support optimized count(*) (without where)"
with "where" you still need to "use index"

explain select count(*) from table
explain select count(*) from table where id=N