homepage Welcome to WebmasterWorld Guest from 54.198.224.121
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe and Support WebmasterWorld
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
JOINS with different purposes - an effort to speed slow queries
noyearzero




msg:4555314
 3:01 pm on Mar 15, 2013 (gmt 0)

So joins have been my best friend for a long time now, but every so often they get complex and slow. After really looking into where the slowness happens and how mysql executes the query, I have realized that joins ultimately serve two purposes.

1) To help decide which rows to show in the result set
2) To help show additional information for each item in that result set.
and ultimately 3) a combination of 1 and 2.

From what I can tell, mysql executes the join statements for every single row in the database before it starts filtering out items from the WHERE and HAVING statements. This causes large tables with complex joins to run very slowly. But this is the way it has to work for scenarios 1 and 3. However if you only need the join to show additional information from the select and it doesn't in any way affect which rows are shown (scenario 2), then it seems there could be a huge performance boost if mysql would execute those joins after the WHERE, HAVING, ORDER BY, and LIMIT statements figured out the final result set.

I realize this is possible to achieve from doing a subquery in the SELECT, but since the premise of this conversation is complex joins this isn't always practical... especially if you need to pull multiple columns from the joined table.

So is there some way to tell mysql when a JOIN can be executed late in the statement?

 

Kendo




msg:4555829
 10:09 am on Mar 17, 2013 (gmt 0)

I don't use joins at all unless I have to. If I need to get additional info from a related table, I use a function applied to the result. Doing it this way may have penalties but as they are only applied to the score of results returned from searching through thousands of results, I assume it to be much more efficient than a join sift of all records.

DrDoc




msg:4570394
 4:13 pm on May 3, 2013 (gmt 0)

You can always use UNIONs and subqueries to determine the order in which queries are run and JOINs applied.

Subqueries run before outer ones (obviously), and queries are executed in order of appearance for UNIONs.

That said -- even complex JOINs on very large tables run fast ... if indexes are carefully applied, and sometimes forced. (May need to ANALYZE TABLE from time to time as well.)

What does EXPLAIN tell you?

Many times you will find that the query runs just fine with the JOINs, but that it's your WHERE or an ORDER BY that messes things up.

For complex queries, consider limiting your result set through JOINs in a subquery, the apply all the ORDER BY and GROUP BY in outer queries, along with appropriate WHERE statements.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved