Optimize long mysql query

Question

I have a query for a chat site I built ages ago and due to large volumes of traffic my poor query design has caught up with me. Here I have an example from my long query log:

SELECT  DISTINCT user.id 
FROM    user 
        STRAIGHT_JOIN user_pics 
            ON user.id=user_pics.uid      
        STRAIGHT_JOIN user_account 
            ON user_account.user_id=user.id 
WHERE   registered = 1  AND 
        user.id<>0 AND 
        user.id<>23847 AND 
        user.id<>12392... (IT HAS LITERALLY 1000 OF THESE)
        AND user_pics.main=1 AND 
        user_pics.approved=1 AND 
        user_pics.deleted<>1 AND 
        gender LIKE '%female%' AND 
        country LIKE '%United Kingdom%' AND 
        city LIKE '%birmingham%' AND 
        sexorientation     LIKE '%Straight%' 
ORDER   BY updatedate DESC 
LIMIT   20;

The query takes about 15 seconds to execute, I have Indexed all the columns of reference as well. Would replacing the 1000 "AND user.id<>0" marks with a lookup into a temp table improve the query. I thought I would ask before going and making the changes. If you can recommend any helpful changes with code I would be hugely grateful.

EDIT: The "user.id<>23847" marks are created in php by a simple select and then a foreach array loop adding them to the larger sql query.

EDIT 2: Thank you for all the help, by using "not in" they query was reduced from 13 seconds to 0.3 seconds.

Answer 1

Try yo use EXPLAIN
http://dev.mysql.com/doc/refman/5.0/en/explain.html
And you will see what can be better.

This can be replaced as user.id NOT IN(23847 ,0 , 23847 ,...) using NOT IN()

Answer 2

Yes, if you were to use,

user.id NOT IN (SELECT id FROM idExemptTable)

this would be much faster than individually checking each id

I've included a link to another answer that goes into further detail regarding the IN statement: -

SQL: SELECT IN faster and best practice?

I'm also unsure why you're using a wildcard match and LIKE for checking country name.