"select * " vs "select col1, col2 "

[james438]

Two questions. Which is faster? select * from table1 where (col1 like term) and (col2 like term).

or is this faster:

select col1, col2 from table1 where (col1 like term) and (col 2 like term).

My queries are dynamic and therefore can get quite large quickly depending on the user. Would it better (ie faster) if I break the queries up into bite size pieces and maybe combine them with a bit of PHP later, or just leave them as is?

[thetestingsite]

I would have to say this would be faster due to the byte size of it.

select * from table1 where (col1 like term) and (col2 like term)

The bigger the total file size, the slower the script will run. Hope this helps.

[james438]

cool, thanks. That actually will allow me to write far smaller queries with the use of arrays to make queries that are maybe 100 characters long as opposed to 2000 or so.

What I was thinking was that the query was sent to the database which receives it, then processes it there. If that were the case I would imaging that it might be faster under certain circumstances to write longer scripts that specify exactly what the server that contains the database should search as opposed to shorter ones where the server must search through the complete table before coming up with the desired results. heh, I guess I could have said all that in my first post.

Either way, thanks for the info and the quick response

[thetestingsite]

Yea, no problem. As far as I know, the entire script must pass through the PHP processor first, then (once it finds the mysql stuff) it goes to that and queries the database. However, testing different scenarios would be the best bet (to figure out what would be faster).

Hope this helps.

[Twey]

That's not a problem, the query is compiled into a binary form before sending and is really rather tiny. The execution's pretty fast too. However, if you have 15,000 rows in table users, which has twelve columns, and you select * when you only want a list of email addresses, you're transferring 12× more data than you need to. Selecting only the columns you need is always more efficient.

[james438]

Ah, thank you. I do have very small tables, but as tables grow I want to be sure that the search queries that I have will search them in the most efficient way. The cols contain fields that can be as large as pages of text or as small as a few words.

Thanks for the help. What you said makes a lot of sense.