MySQL optimization -- SELECT queries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

MySQL optimization -- SELECT queries

Post 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?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: MySQL optimization -- SELECT queries

Post 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
User avatar
Josh1billion
Forum Contributor
Posts: 316
Joined: Tue Sep 11, 2007 3:25 pm

Re: MySQL optimization -- SELECT queries

Post by Josh1billion »

Interesting, thanks for that. :)
vik123
Forum Newbie
Posts: 1
Joined: Tue Jul 15, 2008 9:55 am

Re: MySQL optimization -- SELECT queries

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: MySQL optimization -- SELECT queries

Post 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).
Post Reply