Page 1 of 1
MySQL optimization -- SELECT queries
Posted: Wed Jun 04, 2008 8:55 am
by Josh1billion
Trying to figure out what to optimize first here, mostly focusing on the SELECT queries for now. For SELECT queries, can someone give me an idea on how much an impact the size of the query and the number of queries matter?
Amount of data being selected
How much slower is a "SELECT * FROM `table`" query than a "SELECT `just_one_column` FROM `table`" query in a table that contains a fairly large amount of data (assume around 40 columns, being a mixture of INTegers, VARCHARs, and perhaps a few paragraphs of TEXT).
Time overhead on a single query
How important is the overhead, processing-wise as opposed to memory-wise, per query? For example, how much extra time is there to be saved by combining two queries into one when possible? Any?
Re: MySQL optimization -- SELECT queries
Posted: Wed Jun 04, 2008 4:34 pm
by califdon
My understanding (which is far from complete) is that the response time of a select query is almost totally dependent on how many rows must be returned, so from that standpoint, the number of columns is not a factor. It is certainly a factor in memory usage, of course. As for one vs. two queries, I think it is, once again, the number of rows that must be returned that relates to the response time. Generally, I'm inclined to use a single query whenever it is possible to do so. Multiple queries require that one query's results be stored temporarily in memory, then the second query operates on those results, which I would think is less efficient than letting the database engine's internal optimization determine just how it will really be implemented. If you're not already familiar with MySQL's explain command, I recommend reading up on it:
http://forums.spry.com/showthread.php?t=1345
Re: MySQL optimization -- SELECT queries
Posted: Thu Jun 05, 2008 6:27 am
by Josh1billion
Interesting, thanks for that.

Re: MySQL optimization -- SELECT queries
Posted: Tue Jul 15, 2008 10:22 am
by vik123
Hi, I am having this problem. On search, before the results, I am showing related tags. Wanted to show against each result some number results too (doors(300) , tables(200), ...). The process is slowed down exactly by these tags results. It takes a second for each tag res, and for 10 tags, ... imagine. I was thinking how to optimize this, cause cant let it working like this. I am querying for each tag with: select distinct prods from table1, table2, table3 where concat(col1, col2, col3) regexp "word" and table1.col1 = table2.col2, ... May be there is a faster way.
Re: MySQL optimization -- SELECT queries
Posted: Tue Jul 15, 2008 10:47 am
by Eran
Actually, to my knowledge there is no direct relationship between the amount of columns selected and the response time for a single query. However, selecting multiple columns takes up much more memory - which is especially critical in the db cache. You want your cache primed on as many queries as you possibly can so fetching only relevant columns is important for concurrency (many simultaneous queries).
Regarding many vs. single queries - it is dependent on the actual query. As a general rule of thumb its good to reduce the number of queries, however MySQL query optimization is not as efficient with very complex queries - those would sometime benefit from being broken into several smaller but simpler ones. You have to constantly profile your queries execution time to know for sure. Also, use EXPLAIN liberally to find simple tips on how to optimize your queries (regarding index placements and stopping conditions).