Are multiple sql querys a significant performance hit?

Question

I am using MySql (and PHP).

It's best if I give an example:
I am making a query, say SELECT * FROM a_table WHERE type='visible' then returning the count. Then later I query again but WHERE type='hidden', etc. I may query again to get the full contents. All queries are unique, but are from the same table. I also query multiple other tables with unique queries. (PLUS execution time of mysql_real_escape_string() sprinkled in the queries)

I'm not sure how many queries I perform altogether. But I am worried that if I keep increasing queries, I will start getting into large execution times.

My question is: are large amounts of queries noticeable on performance? If yes, is there a better solution on how to store that data? Is performing an initial query and storing all the rows in an array (large array could be 1000's of rows) and then manipulating the array faster (and/or feasible)? How many queries does this gray area become black and white?

---

Results from 'optimizing my queries'.

These are execution times in seconds:

• No optimization: .358 s
• Return COUNT(*): .321 s
• Reducing my column selection: .266 s

Although, the biggest was not opening a connection during every query. I was idiotically opening and closing a db connection each poll. By changing it to one continuous connection, I got execution time down to .085 s!

Lesson learned. Thanks for all the input!

Answer 1

If you would do this all in one query, PHP would have to do the rest. So, the question is where the bottleneck is. Is the db server machine slower than the webserver machine? What about the network conneciton between them? Best way to optimize it for your conditions is, as I guess, experimenting.

Answer 2

The fewer the queries, the better. Of those, only get the columns you actually need.

Answer 3

Definitely, large queries and multiple queries are performance hit as DB query involves connect, query and disconnect.

For performance improvement, please make sure that you have right flags in place for mysql,conf and there are variables wrt cache and they will prevent you in hitting database if you are referring to same table again and again.

--Cheers

Answer 4

Don't pre-optimize. Obviously the fewer queries, the less overhead in running them, but this overhead is usually very small. The number of queries is not anywhere near as big a factor as properly indexing the tables and optimizing the actual queries themselves.

Generally, each query has to send some data over the network to the listening port on the database process, which parses, compiles and runs the query. Usually, query plan caching will ensure that minimal amount of time is spent doing this. As long as your network isn't bottlenecking anything, the RPC overhead is quite low, on the order of a few milliseconds or less.

Really, it will be dependent on the queries themselves and the situation you're in.

Answer 5

if you want to count your distinct types use the following query:

  SELECT `type`, COUNT(*) AS `count`
    FROM `a_table`
GROUP BY `type`

Answer 6

I agree with other users. The less queries, the better.

On the other hand, consider this. Now days, average CPU speed is more than 1GHz, thats about 1,000,000 clock cycles per second, and in each clock cycles you can get multiple things done. Example would be that ALU (Arithmetic Logic Unit) can perform more than 1 additions per clock cycle.

Taking this into account, having more than 1 queries is not such a big deal. It would take a couple of more milliseconds (at most) to process extra one or two or three queries. Basically the user will probably not even notice this.

Warning though, if you will have like 100 extra queries, yes, this will probably cause some noticeable delays. So the idea is to do things in moderate amounts.