How to optimize the SQL query?

Question

I have an SQL query that I think is not optimized enough. I timed it, and it sometimes take 1.5 seconds to complete, which seems a little high, no?

anyway this is the query:

SELECT id, link, feed, category, description, title, GROUP_CONCAT( tag ) as t, published
            FROM items
            LEFT JOIN tags ON items.id = tags.item_id
            WHERE id NOT IN (
                SELECT item_id
                FROM tags, sinunim
                WHERE tag = name
                AND op = '1'
                AND user = '$user_cookie'
            ) AND id NOT IN (
                SELECT id
                FROM sinunim
                WHERE id <> 0
                AND user = '$user_cookie'
            ) AND id NOT IN (
                SELECT i.id
                FROM  sinunim s, items i
                WHERE s.type =  'category'
                AND s.name = i.category
                AND s.op = '1'
                AND s.user = '$user_cookie'
            ) AND id NOT IN (
                SELECT i.id
                FROM  `sinunim` s, items i
                WHERE s.name = i.feed
                AND s.op = '1'
                AND s.user = '$user_cookie'
            )
            GROUP BY items.title
            ORDER BY items.published DESC
            LIMIT 0 , 50

you should use inner join instead of cross join with where (from Table1, Table 2 where.. is a cross join) — Gonzalo.-
– Gonzalo.-, Commented Aug 16, 2012 at 12:37
No offence, but I think all optimization talks should begin with EXPLAIN results on display. ) And yes, joining by nested queries doesn't seem quite right. — raina77ow
– raina77ow, Commented Aug 16, 2012 at 12:37
@ElVieejo Technically it (cross or inner) depends on criterias specified in WHERE blocks. Still, I agree that rephrasing it would be way more readable. — raina77ow
– raina77ow, Commented Aug 16, 2012 at 12:39
in addition to the EXPLAIN, it would be nice to see what the table definitions are and what indexes are in place as well — ninesided
– ninesided, Commented Aug 16, 2012 at 12:39
also, you are using two times a join between sinunim and items, in two not in clause.. Combine them in just one — Gonzalo.-
– Gonzalo.-, Commented Aug 16, 2012 at 12:41

Mihai Stancu · Accepted Answer · 2012-08-16 12:41:20Z

2

You can rewrite the 4 NOT IN sub queries as one single sub query and since you're grouping by items.title you can even eliminate all sub queries and use joins instead.

That way whatever you join will be joined only once and you can recreate the various comparisons by grouping them in one bigger logical expression (x = y AND y = z) OR (k = l AND m = n).

answered Aug 16, 2012 at 12:41

Mihai Stancu

16.2k2 gold badges35 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mihai Stancu Over a year ago

It should be the most efficient because it allows the SQL Query Planner & SQL Query Optimizer to work efficiently.

Nicholas · Accepted Answer · 2012-08-16 14:06:46Z

To most effectively optimize queries, you should start by identifying the queries that have the longest duration. You can do so by using SQL Profiler. Next, you analyze the queries to determine where they are spending their time and whether they can be improved. You can use the SQL Query Analyzer to help analyze query behavior.

The overall optimization process consists of two main steps:

1) Isolate long-running queries.

2) Identify the cause of long-running queries.

Step 1

You can isolate long-running queries using SQL Profiler.

Step 2

Using SET statements.
Using SQL Query Analyzer options.

To use SQL Query Analyzer

SQL Query Analyzer displays query execution plans in text mode or graphical mode.

1) Start SQL Query Analyzer, connect to the server, and select the database that you are working on.

2) Paste the query into the SQL Query Analyzer window.

3) If you are using SQL Profiler to trace queries, the query text can be copied from the trace window and used within SQL Query Analyzer.

4) On the Query menu, click Display Estimated Execution Plan. The estimated execution plan for the query is displayed. If the query window contains multiple queries, an execution plan is displayed for each query.

5) On the Query menu, click Show Execution Plan, and then run the query in the query window.Execution plans and query results now appear in separate panes of the window so you can view them together.

I hope that this will help you!

Regards,

Nicholas

Somnath Muluk · Accepted Answer · 2012-08-16 12:58:32Z

I tried to minimize 4 Not In queries in where condition. Can you see this works or not?

SELECT id, link, feed, category, description, title, GROUP_CONCAT( tag ) as t, published
            FROM items
            LEFT JOIN tags ON items.id = tags.item_id
            WHERE id NOT IN (
                SELECT case 
                        when ( tag = name AND op = '1' ) then item_id
                        when id <> 0 then id
                       END   
                FROM tags, sinunim
                WHERE user = '$user_cookie'
            )
            AND id NOT IN (
                SELECT i.id
                FROM  sinunim s, items i
                WHERE ( (s.type =  'category'
                AND s.name = i.category) OR s.name = i.feed)  
                AND s.op = '1' 
                AND s.user = '$user_cookie'
            ) 
            GROUP BY items.title
            ORDER BY items.published DESC
            LIMIT 0 , 50

Collectives™ on Stack Overflow

How to optimize the SQL query?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related