Need help with 2 MySql Queries. Join vs Subqueries?

I'm fairly certain that JOIN s are more performant than subqueries. One problem I see is that there aren't any date or foreign key indexes in your table creation scripts. You'll want those.

Here's how I'd accomplish query #2, if I understood your question correctly.

I'm fairly certain that JOINs are more performant than subqueries. One problem I see is that there aren't any date or foreign key indexes in your table creation scripts. You'll want those.

Here's how I'd accomplish query #2, if I understood your question correctly: SELECT u. *, m_new. Id AS have_new, MAX(m_new.

Created_at) AS new_created, m. Id AS have_any, MAX(m. Created_at) AS created FROM USER you LEFT JOIN message AS m_new ON u.Id = m_new.

Sender_id AND m_new. Receiver_id = 1 AND m_new. Read_at IS NULL LEFT JOIN message AS m ON (u.

Id = m. Sender_id AND m. Receiver_id = 1) OR (u.

Id = m. Receiver_id AND m. Sender_id = 1) WHERE m.

Id IS NOT NULL GROUP BY u. Id ORDER BY new_created DESC, created DESC ; Here are a couple of good links in SO on JOINs vs subqueries: SQL: Join vs. subquery Subqueries vs joins.

Thank you very much for the solution, I have not tested it yet, but MAX is what I needed and did not think of. Thank you. Thanks for advice on indexes, I have them in my real tables, this was just for ilustratoin/testing – BugBusterX Dec 28 '10 at 22:41 I'm glad I pointed you in the right direction.

I've also added a couple of good links for JOIN vs subquery. Do you have a good tool for SQL profiling? – Sonny Dec 29 '10 at 14:06.

The first query: SELECT u. *, EXISTS ( SELECT NULL FROM message WHERE sender_id = u. Id AND receiver_id = 1 AND read_at IS NULL ) have_new, EXISTS ( SELECT NULL FROM message WHERE ( sender_id = u.Id AND receiver_id = 1 OR sender_id = 1 AND receiver_id = u.

Id ) AND read_at IS NOT NULL ) have_any FROM user you The second query: SELECT u. *, EXISTS ( SELECT NULL FROM message WHERE sender_id = u.Id AND receiver_id = 1 AND read_at IS NULL ) have_new, ( SELECT created_at FROM message WHERE ( sender_id = u. Id AND receiver_id = 1 OR sender_id = 1 AND receiver_id = u.

Id ) ORDER BY created_at DESC LIMIT 1 ) last_message FROM ( SELECT DISTINCT sender_id AS party FROM message WHERE receiver_id = 1 UNION SELECT DISTINCT receiver_id FROM message WHERE sender_id = 1 ) m JOIN user you ON u.Id = m. Party ORDER BY have_new DESC, last_message DESC Create the following indexes: messages (sender_id, receiver_id, last_message) messages (receiver_id, sender_id, last_message).

Thanks for taking time and helping out. Do you think your version using subqueries would be faster than joins? – BugBusterX Dec 28 '10 at 22:40 @Bugbusterx: yes, provided that you create the appropriate indexes.

– Quassnoi Dec 28 '10 at 22:53.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.