MySQL queries... lots of litlle ones, or a few biggies?

Goodshape

Working with PHP and running queries to a MySQL database..

Just wondering if there's an accepted best practice when grabbing information from a database.... in terms of putting less strain on the server and optimising response times, is it better to run a few big queries ('get * from table') and store the whole lot in a variable, to be called when and if needed, or to run many small specific queries only when needed ('get name from table where id = 2' , 'get role from table where id = 2', etc.)


My current scenario is a web application with groups and connections between users. So I might be getting a list of user-id's from one table (members of a group, for instance) and I find myself running a lot of queries along the lines of "$friend[1] = get title from users where id = $user_id" in a big for-each loop.

Just wondering if it might be better practice to get all the 'users' data into an array first of all, then extract the bits I need in the foreach. Or does it even matter?

misterq

Don't query for any more information than you absolutely have to, but if you are making queries, it is generally better to try and grab as much information at the same time rather than having lots of small queries.

Goodshape

Cool, thanks.

Malice

Definitely don't do a "SELECT * FROM <table>" style query. As misterq said, only query what you need.

My current scenario is a web application with groups and connections between users. So I might be getting a list of user-id's from one table (members of a group, for instance) and I find myself running a lot of queries along the lines of "$friend[1] = get title from users where id = $user_id" in a big for-each loop.

I'm sure there's a way to script that query so that you get all of the group members returned. Maybe you have to join a couple of tables together? It's been a good while since I worked with MySQL but I'm sure it can return an array of results.

Goodshape

Once I get my list of user_id's, I'm pretty sure I can get all required user data at once.. something like "SELECT username, email FROM `users` WHERE id = '$user_id1' or id = '$user_id2'".

I guess the idea of less queries, more focused, makes the most sense. I've just always wondered which actually required most effort : querying and storing the large amounts of data, or querying for small amounts of data lots of times.

Webmonkey

Goodshape wrote:

SELECT username, email FROM `users` WHERE id = '$user_id1' or id = '$user_id2

That depends if the $user field is indexed. This will provide a direct access method to the required data in the file. Otherwise it will have to do a search which may take time and might be just as well to read in all data like a heap.

This is a big part of database systems - do a look on Query Optimization in DBMS - Though MySQL is quite a small database system compared to Oracle/Ingres etc so might not be applicable to that.

Goodshape

Webmonkey wrote: »

That depends if the $user field is indexed.

The 'id' field is a primary index in the user table. Is that what you mean? It's more work to look for a non-indexed value?


And thanks for the replies here.. I've started looking through a few webpages on the subject too. Don't think my code is too far off the mark, actually. Just like to make sure I'm not making any silly unnecessary moves.

Webmonkey

If the 'id' field is the actual user_id field then thats good but if you are often doing look ups on you user_id maybe the user_id should be the primary key and indexed since the user ID will be unique anyways