Welcome to WebmasterWorld Guest from 54.196.244.186

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)

New User

10+ Year Member

joined:July 7, 2004
posts:30
votes: 0


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)

New User

10+ Year Member

joined:Dec 13, 2004
posts:25
votes: 0


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)

New User

10+ Year Member

joined:July 7, 2004
posts:30
votes: 0


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

New User

10+ Year Member

joined:Dec 13, 2004
posts:25
votes: 0


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

New User

10+ Year Member

joined:Dec 13, 2004
posts:25
votes: 0


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)

New User

10+ Year Member

joined:July 7, 2004
posts:30
votes: 0


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)

Administrator

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

joined:July 31, 2003
posts:12533
votes: 0


Maybe this discussion will help shed some light...

[webmasterworld.com...]

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

New User

10+ Year Member

joined:July 7, 2004
posts:30
votes: 0


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

Preferred Member

10+ Year Member

joined:Dec 30, 2003
posts:428
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:July 4, 2001
posts:997
votes: 0



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)

New User

10+ Year Member

joined:July 7, 2004
posts:30
votes: 0


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

New User

10+ Year Member

joined:Apr 9, 2004
posts:16
votes: 0


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)

Preferred Member

10+ Year Member

joined:Nov 19, 2002
posts:372
votes: 0


"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