MySQL Forums
Forum List  »  Performance

long running, can't get to use index
Posted by: Eric Peterson
Date: March 24, 2017 10:10AM

```
SELECT
id,
r_status,
r_type,
r_create_tm,
r_detailsID,
t_user_id
FROM
tbl
WHERE
r_userid IN ( 1, 200 ) AND
r_create_tm < '2017-03-10 07:35:38' AND
r_status NOT IN ( 5, 47, 36, 29, 98, 54, 13 ) AND
r_type NOT IN ( '105', '83' )
ORDER BY
r_create_tm ASC;
```

This is a large-ish table, 42+ million rows, taking much too long. There are 12 indexes useful for other queries, but not this one. In production it keeps grabbing the r_create_tm only index. In Dev it grabs r_userid & r_detailsID index.

I tried various combinations of the above four elements in the WHERE clause but to no avail. I'm guessing because of the two NOT IN clauses.

The app dynamically creates this query, so adding an index hint would be difficult.

Any ideas on how I can re-arrange this query to make use of indexes. Change to a NOT EXIST? or self-join?

Thanks
Eric

Options: ReplyQuote


Subject
Views
Written By
Posted
long running, can't get to use index
153
March 24, 2017 10:10AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.