IN (SELECT...) is poorly optimized; use JOIN instead.
select status,name,surname,address
from database
where status in ('owner','renting')
and userid not in (
SELECT userid
from database
where status in ('evicted','moved'))
and city = 'Detroit';
-->
select a.status, a.name, a.surname, a.address
from database a
left join database b on a.userid = b.userid -- "self join"
where a.status in ('owner','renting')
and b.status in ('evicted','moved'))
and a.city = 'Detroit'
and b.userid is null; -- same effect as NOT IN
You are looking for Detroit owners/renters who have not been kicked out of some other city? And a userid can occur more than once in the table?
Please provide
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
* EXPLAIN SELECT ...\G -- clues of inefficiencies
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
and surround them with [ code ] and [ / code ]
How much RAM do you have?
Assuming that ownerid is not UNIQUE, I would suggest adding two indexes:
INDEX(ownerid, status),
INDEX(ownerid, city, status)