homepage Welcome to WebmasterWorld Guest from 54.211.138.180
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
mysql query speed
"SELECT field1" VS "SELECT field1, field2 ..."
squallions




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

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

 

s1dev




msg:1280167
 9:34 pm on Jan 10, 2005 (gmt 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.

squallions




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

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

s1dev




msg:1280169
 10:10 pm on Jan 10, 2005 (gmt 0)

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

s1dev




msg:1280170
 10:14 pm on Jan 10, 2005 (gmt 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.

squallions




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

coopster




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

Maybe this discussion will help shed some light...

[webmasterworld.com...]

squallions




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

Thank.

SeanW




msg:1280174
 8:29 pm on Jan 14, 2005 (gmt 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

ggrot




msg:1280175
 9:26 pm on Jan 14, 2005 (gmt 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()

squallions




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

Great. Thank.

listerine




msg:1280177
 5:31 pm on Jan 18, 2005 (gmt 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)

Xuefer




msg:1280178
 5:04 am on Jan 20, 2005 (gmt 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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved