How to optimize this query "where IN" and "where NOT IN"?

Question

The following inquiries / inquiries are received by the cities visited by the user, they receive the places where the user visited; and returns places in cities where the user has not been.

// I get the city_id and object_id. Each vote has the place_id and its city_id. SELECT DISTINCT city_id as city_id, object_id as object_id FROM vote WHERE object_model = 'Place' AND user_id = 20 ORDER BY created_at desc // I build an array with city_ids and another with object_ids $city_ids = array(...); $place_ids = array(...); 

I get places where the user was not in the cities where he was - 1 second

  SELECT id, title FROM place WHERE city_id IN ($city_ids) AND id NOT IN ($place_ids) ORDER BY points desc LIMIT 0,20 

EXPLAIN SQL

 select_type table type possible_keys key key_len ref ows Extra ----------------------------------------------------------------------------------------------------------- SIMPLE p range PRIMARY,city_id_index city_id_index 9 NULL 33583 Using where; Using filesort 

Another attempt to optimize is to make one request using LEFT JOIN / IS NULL and a subquery, but it takes much longer (30 + seconds)

  SELECT id, title FROM place AS p LEFT JOIN vote v ON v.object_id = p.id AND v.object_model = 'Place' AND v.user_id = 20 WHERE p.city_id IN (SELECT city_id FROM vote WHERE user_id = 20 AND city_id != 0) AND v.id is null ORDER BY p.points desc LIMIT 0, 20 

How will you make a request / requests, thinking that we can have an array of 500 cities and 1000 places for each user? What is the best alternative to where and where NOT IN when there are many identifiers?

Answer 1

I am not a MySQL expert, but I do not look too complicated. Instead of focusing on the query, I would look at the indexes. Perhaps the following indexes will help:

 CREATE INDEX vote_index1 ON vote (user_id, city_id) CREATE INDEX vote_index2 ON vote (object_id, object_model, user_id) 

Answer 2

If you want to request 2 attributes, you need to join 2 tables and not only 1 table. Also I want to know what is object_id?

 SELECT id, title FROM place AS p LEFT JOIN vote v ON v.object_id = p.id AND v.object_model = 'Place' AND v.user_id = 20 LEFT JOIN place AS P1 on V.city_id = P1.city_id WHERE v.id is null ORDER BY p.points desc LIMIT 0, 20 

Answer 3

Do not use the IN operator, just try to solve by joining all the necessary tables. IN can be done with a normal connection, I believe, and NOT IN you will do, for example:

 select * from a left join b using (field) where b.field is NULL 

this way you get all the records from table a, where there is no corresponding record in table b.

Answer 4

When using mysql, you should remember that it is very stupid when handling IN () subqueries (or something else actually). Therefore, you should rewrite your second attempt as:

 SELECT id, title FROM (SELECT DISTINCT city_id FROM vote WHERE user_id = 20) v JOIN places p USING (city_id) LEFT JOIN vote v2 ON (v2.object_id = p.id AND v2.user_id = 20) WHERE v2.id IS NULL ORDER BY p.points desc LIMIT 0, 20 

Note that "city_id! = 0" is useless since there is a foreign key to votes in cities, so vote.city_id cannot be 0. It can be NULL though.

In addition, the database design is probably incorrect: cities should have their own table, columns "table name + id" are a bad idea, etc.