Big query or multiple queries?

[DavidT]

Hi there,

I'm creating for my website a system that allows logged user to send a "mail", which is actually a mysql database entry. Through a simple form, user can insert subject and message, which will be stored and can be viewed by the website crew.

 

The entry has an additional entry, the user id, from which we can get the user mail, name etc.

 

Now I'm preparing the page where the crew can see the messages, which should create a table with the classic fields, such as:

time&date - subject - user's username - user's mail

 

I do a query to retrieve the message informations, and a loop to create the table. The problem is the following: since the user's nickname and mail are stored in a different table of the database, I have two choices:

1) for each message I create a new mysql query which gets the nickname and male associated to the stored id

2) I store all the id's of the showed messages, then I create a single query to get all the data together (something like SELECT * FROM `usr_messages` WHERE `id` = x1 OR `id` = x2 etc...) and then handle it through the script

 

Which one is more performing?

 

Thanks in advance

[jskywalker]

or

 

3) use JOIN  (http://dev.mysql.com/doc/refman/5.1/en/join.html)

 

i think 3 is the best option....

[DavidT]

Great, thank you very much!

Sorry for the very beginner question. I used mysql/php fo a while, but since I mostly need only the same, usual tasks, I've never actually heard of this joining system (I used LEFT JOIN by the way).

 

Thanks again!

[DavidT]

I am now thinking one more thing. I use LIMIT 0,15 to limit the number of messages shown in the first page, however, I want that the script also gives me a number of the total messages.

I cannot just use php to count the given elements, since this is limited to 15 because of the query string: is there a smarter way that doing two different queries, adding first "count" and then without it using LIMIT?

[Mchl]

You can use SQL_CALC_FOUND_ROWS modifier and FOUND_ROWS() function

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

 

Although some benchmarks suggest that it's better to just run another query with COUNT() (providing certain conditions are met)

http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

[DavidT]

Thanks a lot for both links!