Welcome to WebmasterWorld Guest from 54.159.250.110

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

mysql query speed

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

   
9:29 pm on Jan 10, 2005 (gmt 0)

10+ Year Member



Is it true that the more fields to select in the SELECT query, it will slow down the query?
9:34 pm on Jan 10, 2005 (gmt 0)

10+ Year Member



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.

10:05 pm on Jan 10, 2005 (gmt 0)

10+ Year Member



how about mysql_num_rows() compare to COUNT(*)?
10:10 pm on Jan 10, 2005 (gmt 0)

10+ Year Member



If you just want to know how many, use count(*). It is faster.
10:14 pm on Jan 10, 2005 (gmt 0)

10+ Year Member



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.
7:29 am on Jan 11, 2005 (gmt 0)

10+ Year Member



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.

2:15 pm on Jan 13, 2005 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Maybe this discussion will help shed some light...

[webmasterworld.com...]

4:05 pm on Jan 14, 2005 (gmt 0)

10+ Year Member



Thank.
8:29 pm on Jan 14, 2005 (gmt 0)

10+ Year Member



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

9:26 pm on Jan 14, 2005 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member




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

12:35 am on Jan 15, 2005 (gmt 0)

10+ Year Member



Great. Thank.
5:31 pm on Jan 18, 2005 (gmt 0)

10+ Year Member



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)

5:04 am on Jan 20, 2005 (gmt 0)

10+ Year Member



"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