Как ускорить запрос mysql?

Question

[WebDev]

Есть авторы постов (entities), их посты (posts), пользователи(users), и прочитанные пользователями посты (user_post).
Нужно у каждого пользователя вывести всех авторов и количество непрочитанных постов в зависимости от включенных фильтров (страна, тип постов и тп).

explain 
select `entities`.`id`, `entities`.`type`, COUNT(posts.id) as unread 
        from `posts` 
        inner join `entities` on `entities`.`id` = `posts`.`entity_id` 
        left join `post_user` on `post_user`.`post_id` = `posts`.`id` and `post_user`.`user_id` = 2 
        where `entity_id` in (/*Около тысячи ID, зависит от конкретного пользователя*/)
        and `posts`.`post_type` in ('facebook', 'twitter', 'youtube', 'vevo', 'itunes', 'amazon', 'soundcloud', 'bit', 'ticketfly', 'festival', 'event', 'media') 
        and (`posts`.`created_at` >= NOW() - INTERVAL 3 MONTH or `posts`.`post_type` not in ('facebook', 'twitter')) 
        and `posts`.`post_type` is not null 
        and `posts`.`deleted` is null 
        and `posts`.`is_duplicate` = 0 
        and (((`posts`.`google_country` = 'Russia') or (`posts`.`google_country` = 'Germany' and `posts`.`google_city` in ('Berlin', 'Burghausen')) or (`posts`.`google_country` = 'Poland' and `posts`.`google_city` in ('Warsaw')) or (`posts`.`google_country` = 'United Kingdom' and `posts`.`google_city` in ('London'))) or `posts`.`post_type` not in ('ticketfly', 'bit', 'event'))
        and `post_user`.`id` is null 
        group by `entities`.`id`

Очень долго такой код выполняется, вот что выдает explain

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	bq_entities	range	PRIMARY	PRIMARY	4	NULL	744	Using where; Using temporary; Using filesort
1	SIMPLE	bq_posts	range	posts_entity_id_amazon_asin_unique,posts_entity_id_festival_id_unique,posts_created_at_post_type_is_duplicate_deleted_entity_id_index,posts_entity_id_post_type_is_duplicate_deleted_index,google_country	posts_entity_id_post_type_is_duplicate_deleted_index	9	NULL	1154630	Using index condition; Using where; Using join buffer (flat, BNL join)
1	SIMPLE	bq_post_user	eq_ref	post_user_user_id_post_id_unique,post_id_user_id	post_user_user_id_post_id_unique	12	const,bqmaster.bq_posts.id	1	Using where; Using index; Not exists

Не знаю как правильно вставить результат из explain, надеюсь, что читабельно.
Помогите, пожалуйста ускорить.

UPD:
Упрощенный запрос

explain 
select `bq_entities`.`id`, `bq_entities`.`type`, COUNT(bq_posts.id) as unread 
        from `bq_posts` 
        inner join `bq_entities` on `bq_entities`.`id` = `bq_posts`.`entity_id` 
        left join `bq_post_user` on `bq_post_user`.`post_id` = `bq_posts`.`id` and `bq_post_user`.`user_id` = 2 
        where  `bq_posts`.`deleted` is null
        and `bq_posts`.`is_duplicate` = 0
        and `bq_post_user`.`id` is null
        group by `bq_entities`.`id`\G

И explain из консоли

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bq_entities
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 169072
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: bq_posts
         type: ref
possible_keys: posts_entity_id_amazon_asin_unique,posts_entity_id_festival_id_unique,posts_entity_id_post_type_is_duplicate_deleted_index
          key: posts_entity_id_post_type_is_duplicate_deleted_index
      key_len: 4
          ref: bqmaster.bq_entities.id
         rows: 71
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: bq_post_user
         type: eq_ref
possible_keys: post_user_user_id_post_id_unique,post_id_user_id
          key: post_user_user_id_post_id_unique
      key_len: 12
          ref: const,bqmaster.bq_posts.id
         rows: 1
        Extra: Using where; Using index; Not exists

И скажите, пожалуйста, почему в консоли у rows одно значение, а в веб интерфейсе (adminer) другое? Чему верить?

UPD2:
Если убрать строку and `bq_posts`.`post_type` is not null, то rows меняется с миллиона на 71.

Comments

[FanatPHP]

Выполни запрос в консоли, только вместо точки с запятой поставь в конце \G
Тогда результат експлейна получится более читабельным.

[FanatPHP]

Кроме этого упрости запрос, убирая ненужные детали до тех пор, пока эксплейн остается прежним

Answer 1

[Adamos]

AND могут быть не только в WHERE, но и в ON.
Если в них не результаты, а поля - именно там им и место.
Ну, и чудовищный IN и длинное сочетание AND-OR стоит превратить в подзапрос, имхо.
Может быть, и остальные условия собрать в подзапрос перед JOIN, если они отсекают достаточно много полей....

Answer 2

[Дмитрий Миронов]

Если я правильно понял то left join `bq_post_user` можно сменить наinner join `bq_post_user`, тогда можно убрать вот это условие and `bq_post_user`.`id` is null

По поводу rows - это примерное значение, ему в любом случае на 100% верить не стоит, только прислушиваться )