FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


How to combine lots of queries to one - MySQL





imagefree
I am running a site (take example of a forum), there users have lots of messages per user. Messages are categorized.

The Query i use is

Code:
SELECT message_id , message_text , message_user_id , message_category_id FROM messages WHERE message_id > '10' AND message_id < '51'



This takes 40 records. Now i have to get the records of members and category (for each and every message). This will require 40*2 more queries.

Code:

$categoryid = $row['message_category_id'];
SELECT * from categories WHERE category_id = '$categoryid' LIMIT 1

$userid = $row['message_user_id'];
SELECT * from users WHERE user_id = '$userid' LIMIT 1


At this time the database is not too large and its working well but, probably after having several thousands records, it will start taking seconds to run all these queries.

Is there any way to optimize these queries so that i get all the relevent data from just one query.
Please also tell me how to handle such retrieved data so that i can differentiate between the data from different tables.

I am not good with queries, so please explain it too.

Thanks
kacsababa
Short on time, short answer. ^^
Try JOIN/LEFT JOIN or create VIEWs.
kv
Code:
SELECT *
FROM messages, categories, users
WHERE message_id > '10' AND message_id < '51'
AND messages.message_user_id=users.user_id
AND messages.message_category_id=categories.category_id


This returns rows where tables are joined. You have unique names for fields across tables . So you don't have issue differentiating columns between table. For example user_id in messages table is message_user_id. Use them as is in php to get values from $row.

If you had same name for column in different tables that are joined, you could use alias in the SELECT clause.

For example, if you had user_id column in both messages and users, then

Code:
SELECT messages.user_id as muserid, users.user_id as uuserid,.....
and so on. You get an idea. In this case, you have to use 'muserid' and 'uuserid' in your php code to get value from $row.
urtrivedi
Use following query
Code:

SELECT a.message_id ,a. message_text , a. message_user_id , a.message_category_id,
 b.message_category_text, c.user_name
 FROM messages a
left outer join categories b on a.message_category_id= b.message_category_id
left outer join users c on a.message_user_id=c.user_id
WHERE a.message_id > '10' AND  a.message_id < '51'


Here You need to change spelling of column according to you categories and user table
You can directly retrived values from $row arrary
$username=$row['user_name'];
$categortext=$row['message_category_text'];

After little modification above query will run sucessfully, If you still find any problem let me know.
sheedatali
What the previous posters said, use joins mate to return results from multiple tables.
imagefree
thanks you all. Special Thanks to KV, your solution fits my needs.
I need a little extra explanation of the query:

Code:
SELECT *
FROM messages, categories, users
WHERE message_id > '10' AND message_id < '51'
AND messages.message_user_id=users.user_id
AND messages.message_category_id=categories.category_id


Let me explain how it works and then tell me where i am wrong.

It selects everything from 3 tables (messages, categories and users) under the condition that the message_id in the messages table must be between 10 and 51.
ANd then fetch only that part of data from other tables that meets the equality criteria given in the end of query. Rite?

So if i am rite, if we change the location of the <> conditions and place it to the end of the query, does it effect the results from other tables (other than messages)? or is the part
Code:
messages.message_user_id=users.user_id
AND messages.message_category_id=categories.category_id

executed at the last when all other conditions are met?
Stubru Freak
imagefree wrote:
thanks you all. Special Thanks to KV, your solution fits my needs.
I need a little extra explanation of the query:

Code:
SELECT *
FROM messages, categories, users
WHERE message_id > '10' AND message_id < '51'
AND messages.message_user_id=users.user_id
AND messages.message_category_id=categories.category_id


Let me explain how it works and then tell me where i am wrong.

It selects everything from 3 tables (messages, categories and users) under the condition that the message_id in the messages table must be between 10 and 51.
ANd then fetch only that part of data from other tables that meets the equality criteria given in the end of query. Rite?

So if i am rite, if we change the location of the <> conditions and place it to the end of the query, does it effect the results from other tables (other than messages)? or is the part
Code:
messages.message_user_id=users.user_id
AND messages.message_category_id=categories.category_id

executed at the last when all other conditions are met?


I don't think the result will be different, but I guess it executes them in order, so performance won't be as good.
rockacola
kv's solution provide the same result to a JOIN query, except not using join and is slower in theory.

Query from multiple table
Code:

SELECT *
FROM messages, categories, users
WHERE message_id > '10' AND message_id < '51'
    AND messages.message_user_id=users.user_id
    AND messages.message_category_id=categories.category_id



Query with JOIN
Code:

SELECT *
FROM messages
JOIN categories ON categories.category_id = messages.message_category_id
JOIN users ON users.user_id = messages.message_user_id
WHERE message_id > '10' AND message_id < '51'



Depends on your database design, you may want to show messaages that has no category or posted by unknown user.
Query with LEFT JOIN
Code:

SELECT *
FROM messages
LEFT JOIN categories ON categories.category_id = messages.message_category_id
LEFT JOIN users ON users.user_id = messages.message_user_id
WHERE message_id > '10' AND message_id < '51'


If you need more explainations on different type of JOINs or why you should avoid selecting multiple tables, there's plenty of resources and discussion online, just google it.
Wink
kv
Most of the recent sql engines are clever enough to optimize the queries when preparing execution plan. When there is a cross-product join with a condition, it produces execution plan similar to JOIN..ON. Atleast in theory, there should be no difference in performance.
Related topics
How To : Improve Your PHP Programming
do you think you resemble any book character?
Need some php/mysql dlls
MySQL SUBselect queries and Nested queries
MySQL error 1604
What is useful way in this php+mysql problem?
MySQL connection
MySql to SQL
[php/SQL] Help needed
A very good PHP MySQL Tutorial
Well. Here I Am.
1,000,000 MySQL queries!
PHP&MySql - Is there any server side caching of queries?
Creating tables with Mysql queries
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.