2

I'm currently up to try to improve the performance of some of my queries. As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery on an indexed field without conditions is used.

SELECT * FROM table1 WHERE field1 NOT IN (SELECT index_field FROM table2)

When it comes to use conditions at table2, the query becomes realy slow on a large amount of data.

SELECT *
FROM table1
WHERE
    field1 NOT IN (
        SELECT
            index_field
        FROM table2
        WHERE
            user_id = '2'
    )

I was thinking about using LEFT Join but when I need to to the filtering for the user_id, it also becomes slow.

How may I solve this problem? Currently, I've no clue about that.

Kind regards,

Dominik

//--- EDIT -->

The original query looked sth. like this

SELECT
    i.*
FROM stream_item si
LEFT JOIN user__publisher_item ui
    ON ui.user_id = 2
    && ui.item_id = si.item_id
INNER JOIN item i
    ON i.id = si.item_id
WHERE 
    si.stream_id IN (5,7,8,9,19,24,29,42,43,44)
    && ui.id IS NULL || (
        ui.`read` = 0 || ui.saved = 1
    )
GROUP BY
    si.item_id
ORDER BY
    si.`found` DESC
LIMIT 0,10

I have a table which represents the n:m relationship between stream and items. An unique item can be accessible via multiple streams. Based upon this i have a state-table for user dependent states of an item. Currently, the user-state-entry will only be generated, if the item was read or saved the first tim. Otherwise, there is no entry in the user_item table. In the beginning, this query was fine but now, it takes a lot of time. Even if I leave out the "INNER JOIN" it takes 7 seconds on the current tables with around 500k rows each.

//--- Edit 2 --> The table-structure in the background is as follows:

  • stream
  • stream_item //n:m relation between stream and items
  • item //unique items
  • stream_group //user-defined groups of multiple streams
  • stream_group_streams //_n:m relation between stream_groups and streams_
  • user
  • user_item //state-table that holds the user-dependent states of an item - if there is no state, no row exists for an item

btw. sorry for my bad english

6
  • 1
    LEFT JOIN ON t2.index_field = t1.field1 WHERE t2.user_id != 2 - is this slow? I think this is the best you can do...
    – sashkello
    Jul 4, 2013 at 5:26
  • I'm not sure anybody can provide very meaningful suggestions given that there is very little detail about what you are trying to achieve.
    – Gian
    Jul 4, 2013 at 5:28
  • what about WHERE NOT EXISTS insetad of NOT IN? are they Same?
    – Dinup Kandel
    Jul 4, 2013 at 5:29
  • 3
    I think EXISTS will be faster because once the engine has found a hit, it will quit looking as the condition has proved true. With IN it will collect all the results from the subquery before further processing
    – Dinup Kandel
    Jul 4, 2013 at 5:30
  • give me a second, I'll a more detailed query to the main posting Jul 4, 2013 at 5:47

1 Answer 1

1

SELECT * FROM table1 t1

LEFT JOIN table2 t2 ON t1.field1 = t2.index_field

WHERE

   t2.user_id = '2'

this will be optimized and will serve the purpose! although u can use EXIST as well!

1
  • Thank you for your suggestion. As Gian suggestet, I provided more details in the main-posting now. Sorry for not doing that before. Jul 4, 2013 at 6:03

Not the answer you're looking for? Browse other questions tagged or ask your own question.