[SOLVED] multiple queries vrs joins

[jaymc]

I have a members profile page which contains various bits of data

 

all of the data is spread accross different tables

 

For instance, username, fullname, email, registration date is all in a members table

members personal info is in another

there friends list is in another

people who have viewed there profile in another

 

Etc etc...

 

At the moment i have a query for each of the tables I need to access, so roughly around 9 queries im running to get all the data from accross the database I need

 

Im currently learning joins, my question is, where possible is it better to use joins to grab data from accross tables to put it all in one row, or, in some cases is it better to just query the table seperate

 

Things that Im unsure of is will having 4 joins in a query lock a table as the query is taking longer to fully complete as apposed to 9 queries that all take 0.04 seconds

 

Feedback would be great

[jaymc]

Here is an example of 2 ways of getting data from 2 tables

 

Method 1 (2 Queries)

$query="SELECT `points`, `gender`, `lastlogin`, `lastact`, `registered`, `message` FROM `members` WHERE `username` = 'John' LIMIT 0,1";

 

$querya="SELECT `ID` FROM `messages` WHERE `TO` = '$User_Session' AND `READ` < '1' LIMIT 0,1";

 

 

Method 2 (1 Query)

SELECT members.points, members.gender, members.lastlogin, members.lastact, members.registered, messages.READ as mess FROM members LEFT JOIN messages ON members.username = 'John' AND messages.TO = 'John' AND messages.READ < 1 LIMIT 0,1

 

 

Both of the above methods work although I suspect the supposedly optimize one with the LEFT join in isnt so optimized as its appearing in my process list, even though its only taking a second dd. However as I said it is basically querying 2 tables in one query so its going to take longer. Just wondering if method 1 is faster than method 2

[jaymc]

Anyone?

[effigy]

In regards to the internals of it all, I'm honestly not sure. From a personal standpoint, I always do as much as I can with as little as possible (i.e., less queries). Do you have a unique index on members.username? This should improve the join.

[jaymc]

Yes I have an index on it

 

I just see everywhere people using joins, yeh its better for structured code but in reality, it still has to query that table, so if Im happier having 2 seperate queries which does the same job..

 

Im just waiting for someone to come in and tell me why a join is always better than a query for each table you need data from..?

[effigy]

When the number of tables increase, joins are required. Joins are also helpful for creating modular code. In your example you have written extra, specialized code in order to break one process into two pieces. If you encapsulated everything you need into the query, it would be easier to manage and you could feed it to various functions easily.

[Illusion]

apart from that your ON condition should be something like this.

 

......... ON members.username =messages.TO  AND members.username='John' AND messages.READ < 1 LIMIT 0,1;

 

 

if you use ON members.username = 'John' AND messages.TO = 'John'  , you should have index on both the columns members.username and messages.TO.

[jaymc]

So to wrap it up, for optimization solely, is it better to have a join to get data from 2 tables in 1 query

 

Or, 2 queries and manage them using a bit of PHP

 

I dont care about passing them to functions etc, database load is killing me at the moment so I must optimize

[effigy]

How did you determine that this was a problem? Are you using joins elsewhere? What does an EXPLAIN tell you? What about a slow query log? Perhaps it needs some configuration tweaks?

[jaymc]

Slow query log is no good, Most of my queries are executed in mili seconds, however, a large amount of fast queries do add up

 

Im just asking as a general rule to follow

[effigy]

In general, I say use joins. That's why databases exist: to break apart pieces of data via normalization, ID them, then write queries to join them back together.

[jaymc]

I'll go with it joins then!

 

Thanks