CODEWITHSUNDEEP
sqlmysqlquery-optimization
aiternal
aiternal

Reputation: 1100

What is the better way than joining 3 tables?

I have to join 3 tables somewhere on my project.
Here is example tables and columns:

Table-1 : posts 
Columns1: id,owner,title,post,keywords

Table-2 : sites
Columns2: id,name,url

Table-3 : views
Columns3: id,post,view

When I join all these tables it happens such a little huge query:

SELECT title,post,keywords,name,url,view
FROM posts 
LEFT JOIN sites ON sites.id=posts.owner 
LEFT JOIN views ON views.post = post.id 
WHERE posts.date BETWEEN '2010-10-10 00:00:00' AND '2010-11-11 00:00:00' 
ORDER BY views.view DESC 
LIMIT 0,10

Is it the only way or could I do something else to get better performance?

This is my current query's EXPLAIN. Above one is just an example. MysqlFront EXPLAIN Result

Upvotes: 0

Views: 123

Answers (4)

Russell Steen
Russell Steen

Reputation: 6612

That's not a particularly "Huge" query. Have you ran query analyzer and checked for where the slow point is and then checked your indexes?

Re: Analyzer - Microsoft keeps moving it, but in 2008 Management Studio there are several options for showing the execution plan. Once you see the execution plan you can see where the problems are. Look for a single action taking 80+% of your time and focus on that. Things like Table Scans are an indication that you could speed it up by tweaking indexes. (There are downsides to indexes as well, but worry about that later).

Upvotes: 2

nybbler
nybbler

Reputation: 4841

If your relations are guaranteed, in other words non-nullable foreign keys, then the query will perform better if it uses inner joins instead of left joins. Although this query does not appear to be large or complex enough to seriously be suffering from performance issues.

Upvotes: 0

Babak Naffas
Babak Naffas

Reputation: 12581

If your POSTS table is particularly large (>100K rows?) then one thing you could do is to load the time-filtered posts into a temporary table and join on that temp table.

Upvotes: 0

philo
philo

Reputation: 3690

That's not a huge query by any stretch of the imagination.

How slow is it really?

Maybe if views doesn't contain any more information than what you've shown, you should just have a view count be a field of posts. No need for it to be its own separate table unless you're actually storing some information about the views themselves, like user-agent strings or time.

Upvotes: 2

Related Questions