VPF::В запросе 18 left join - Форум программистов

Лисеночек

Дата 11.10.2010, 13:15 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 32
Регистрация: 12.11.2004

Репутация: нет
Всего: нет

Доброе время суток.
СУБД: MySQL 5.1.48. Сервер: Intel Xeon 2 Ghz, RAM 2 Gb, Centos.
Есть такой запрос:

Код


      select o.*, t.name as name_type, s_t.name as name_supertype, u_a.login as login_user_add,
      u_e.login as login_user_edit, r.name as name_region, str.name as name_street,
      st.name as name_state, m.name as name_metro, rz.name as name_razdel, srv.name as name_service,
      mt.name as name_material, i.name as name_infosource, i_ex.name as name_infosource_exclusive,
      obn.name as name_mediator, group_concat(p.num_phone) as phones, cast(group_concat(ons.id_site) as CHAR) as id_site,
      group_concat(distinct u_i.login) as login_user_image
      from object_n o
      left join types t on o.id_type=t.id
      left join types s_t on o.id_supertype=s_t.id
      left join users u_a on o.id_user_add=u_a.id
      left join users u_e on o.id_user_edit=u_e.id
      left join regions r on r.id=o.id_region
      left join streets str on o.id_street=str.id
      left join state st on o.id_state=st.id
      left join metro m on o.id_metro=m.id
      left join razdel rz on rz.id=o.id_razdel
      left join services srv on srv.id=o.id_service
      left join materials mt on mt.id=o.id_material
      left join infosource i on i.id=o.id_infosource
      left join infosource i_ex on i_ex.id=o.id_infosource_exclusive
      left join object_n obn on obn.id=o.id_mediator
      left join phones p on p.id_object_n=o.id and p.is_request=0
      left join object_n_site ons on ons.id_object_n=o.id
      left join images im on im.id_object_n=o.id and im.type_img='images'
      left join users u_i on u_i.id=im.id_user_add
      where o.id_supertype='39' and o.is_deleted='0' and o.id_type<>0
      group by o.id
      order by o.id desc
      LIMIT 0, 10

Он выполняется в течении 14 сек на localhost и где-то 17 сек на хосте.
Скорость не зависит от того - есть LIMIT или нет.
Скрипты таблиц (только поля, используемые в запросе, без PK и FK):

Код


      CREATE TABLE IF NOT EXISTS `object_n` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `id_supertype` int(11) NOT NULL,
        `id_type` int(11) NOT NULL,
        `id_user_add` int(11) NOT NULL,
        `id_region` int(11) NOT NULL,
        `id_street` int(11) NOT NULL,
        `id_state` int(11) NOT NULL,
        `id_metro` int(11) NOT NULL,
        `id_razdel` int(11) NOT NULL,
        `id_service` int(11) NOT NULL,
        `id_material` int(11) NOT NULL,
        `id_infosource` int(11) NOT NULL,
        `id_mediator` int(11) NOT NULL,
        `id_user_edit` int(11) NOT NULL,
        `count_room` int(11) NOT NULL,
        `num_floor` int(11) NOT NULL,
        `count_floor` int(11) NOT NULL,
        `num_house` varchar(50) NOT NULL,
        `num_korpus` int(11) NOT NULL,
        `num_apart` varchar(50) NOT NULL,
        `ref_point` varchar(255) NOT NULL,
        `price` float NOT NULL,
        `is_phone` int(1) NOT NULL,
        `is_photo` int(1) NOT NULL,
        `remark1` varchar(255) NOT NULL,
        `remark2` varchar(255) NOT NULL,
        `is_on_site` int(1) NOT NULL,
        `is_deleted` int(1) NOT NULL,
        `date_add` datetime NOT NULL,
        `dle` datetime NOT NULL,
        `square_all` float NOT NULL,
        `square_live` float NOT NULL,
        `square_kitchen` float NOT NULL,
        `count_balcone` int(11) NOT NULL,
        `count_balcone_glass` int(11) NOT NULL,
        `is_exchange` int(1) NOT NULL,
        `formula_exchange` varchar(255) NOT NULL,
        `id_infosource_exclusive` int(11) NOT NULL,
        `name` varchar(255) NOT NULL,
        `flag_admin` int(1) NOT NULL COMMENT '0-new_add, 1-checked, 2-not checked',
        `sum_seller` float NOT NULL);

      CREATE TABLE IF NOT EXISTS `types` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(255) NOT NULL);

      CREATE TABLE IF NOT EXISTS `users` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `login` varchar(255) NOT NULL);

      CREATE TABLE IF NOT EXISTS `regions` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(255) NOT NULL);

      CREATE TABLE IF NOT EXISTS `streets` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(255) NOT NULL);

      CREATE TABLE IF NOT EXISTS `state` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(255) NOT NULL);

      CREATE TABLE IF NOT EXISTS `metro` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(255) NOT NULL);

      CREATE TABLE IF NOT EXISTS `razdel` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(255) NOT NULL);

      CREATE TABLE IF NOT EXISTS `materials` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(255) NOT NULL);

      CREATE TABLE IF NOT EXISTS `infosource` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(255) NOT NULL);

      CREATE TABLE IF NOT EXISTS `phones` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `id_object_n` int(11) NOT NULL,
        `num_phone` varchar(30) NOT NULL);

      CREATE TABLE IF NOT EXISTS `object_n_site` (
        `id` int(11) NOT NULL,
        `id_object_n` int(11) NOT NULL,
        `id_site` int(11) NOT NULL,
        `id_user_add` int(11) NOT NULL);

      CREATE TABLE IF NOT EXISTS `images` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `id_object_n` int(11) NOT NULL,
        `type_img` varchar(10) NOT NULL,
        `id_user_add` int(11) NOT NULL);

Индексы:
object_n:

Код


 KEY `id_type` (`id_type`),
  KEY `id_region` (`id_region`),
  KEY `id_street` (`id_street`),
  KEY `id_state` (`id_state`),
  KEY `id_metro` (`id_metro`),
  KEY `id_usefull` (`id_usefull`),
  KEY `id_gaz` (`id_gaz`),
  KEY `id_water` (`id_water`),
  KEY `id_razdel` (`id_razdel`),
  KEY `id_service` (`id_service`),
  KEY `id_material` (`id_material`),
  KEY `id_infosource` (`id_infosource`),
  KEY `id_mediator` (`id_mediator`),
  KEY `id_part_house` (`id_part_house`),
  KEY `id_firm_build` (`id_firm_build`),
  KEY `id_sewerage` (`id_sewerage`),
  KEY `id_kommun` (`id_kommun`),
  KEY `id_pattern_own` (`id_pattern_own`),
  KEY `id_part_square` (`id_part_square`),
  KEY `id_infosource_exclusive` (`id_infosource_exclusive`),
  KEY `is_deleted` (`is_deleted`),
  KEY `id_supertype` (`id_supertype`),
  KEY `id_user_add` (`id_user_add`),
  KEY `id_user_edit` (`id_user_edit`)

images:

Код


  KEY `id_object_n` (`id_object_n`),
  KEY `id_user_add` (`id_user_add`)

phones:

Код


        KEY `num_phone` (`num_phone`),
        KEY `id_object_n` (`id_object_n`)

Что выдает explain:

Код


      id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
      1   SIMPLE  o   ref     id_type,is_deleted,id_supertype     id_supertype    4   const   30539   Using where; Using temporary; Using filesort
      1   SIMPLE  t   eq_ref  PRIMARY     PRIMARY     4   ekr.o.id_type   1    
      1   SIMPLE  s_t     eq_ref  PRIMARY     PRIMARY     4   ekr.o.id_supertype  1    
      1   SIMPLE  u_a     eq_ref  PRIMARY     PRIMARY     4   ekr.o.id_user_add   1    
      1   SIMPLE  u_e     eq_ref  PRIMARY     PRIMARY     4   ekr.o.id_user_edit  1    
      1   SIMPLE  r   eq_ref  PRIMARY     PRIMARY     4   ekr.o.id_region     1    
      1   SIMPLE  str     eq_ref  PRIMARY     PRIMARY     4   ekr.o.id_street     1    
      1   SIMPLE  st  eq_ref  PRIMARY     PRIMARY     4   ekr.o.id_state  1    
      1   SIMPLE  m   eq_ref  PRIMARY     PRIMARY     4   ekr.o.id_metro  1    
      1   SIMPLE  rz  eq_ref  PRIMARY     PRIMARY     4   ekr.o.id_razdel     1    
      1   SIMPLE  srv     eq_ref  PRIMARY     PRIMARY     4   ekr.o.id_service    1    
      1   SIMPLE  mt  eq_ref  PRIMARY     PRIMARY     4   ekr.o.id_material   1    
      1   SIMPLE  i   eq_ref  PRIMARY     PRIMARY     4   ekr.o.id_infosource     1    
      1   SIMPLE  i_ex    eq_ref  PRIMARY     PRIMARY     4   ekr.o.id_infosource_exclusive   1    
      1   SIMPLE  obn     eq_ref  PRIMARY     PRIMARY     4   ekr.o.id_mediator   1    
      1   SIMPLE  p   ref     id_object_n     id_object_n     4   ekr.o.id    2    
      1   SIMPLE  ons     ALL     id_object_n     NULL    NULL    NULL    1    
      1   SIMPLE  im  ref     id_object_n     id_object_n     4   ekr.o.id    2    
      1   SIMPLE  u_i     eq_ref  PRIMARY     PRIMARY     4   ekr.im.id_user_add  1

В таблице object_n 71748 записей, в phones - 182086, в images и object_n_site пока мало, но кол-во будет расти. Все таблицы кроме object_n, phones, images и object_n_site - справочники, и их объем не будет расти.
Я пыталась записать запрос с использованием подзапроса:

Код


      select o.*, t.name as name_type, s_t.name as name_supertype, u_a.login as login_user_add,
      u_e.login as login_user_edit, r.name as name_region, str.name as name_street, st.name as name_state,
      m.name as name_metro, rz.name as name_razdel, srv.name as name_service, mt.name as name_material,
      i.name as name_infosource, i_ex.name as name_infosource_exclusive, obn.name as name_mediator,
      group_concat(p.num_phone) as phones, cast(group_concat(ons.id_site) as CHAR) as id_site,
      group_concat(distinct u_i.login) as login_user_image
      from (select * from object_n where id_supertype='39' and is_deleted='0' and id_type<>0) o
      left join types t on o.id_type=t.id left
      join types s_t on o.id_supertype=s_t.id
      left join users u_a on o.id_user_add=u_a.id
      left join users u_e on o.id_user_edit=u_e.id
      left join regions r on r.id=o.id_region
      left join streets str on o.id_street=str.id
      left join state st on o.id_state=st.id
      left join metro m on o.id_metro=m.id
      left join razdel rz on rz.id=o.id_razdel
      left join services srv on srv.id=o.id_service
      left join materials mt on mt.id=o.id_material
      left join infosource i on i.id=o.id_infosource
      left join infosource i_ex on i_ex.id=o.id_infosource_exclusive
      left join object_n obn on obn.id=o.id_mediator
      left join phones p on p.id_object_n=o.id and p.is_request=0
      left join object_n_site ons on ons.id_object_n=o.id
      left join images im on im.id_object_n=o.id and im.type_img='images'
      left join users u_i on u_i.id=im.id_user_add
      group by o.id
      order by o.id desc
      LIMIT 0, 10

Но в результате вообще ничего не выводится, и ошибки, ни пустого результата, вообще ничего, как-будто я не нажимала на кнопку "выполнить запрос".
Заранее благодарна.

Это сообщение отредактировал(а) Лисеночек - 11.10.2010, 13:35

Akella

Дата 11.10.2010, 13:21 (ссылка)

(нет голосов)

Загрузка ...

Творец

Профиль
Группа: Модератор
Сообщений: 18485
Регистрация: 14.5.2003
Где: Корусант

Репутация: нет
Всего: 329

Цитата(Лисеночек @ 11.10.2010, 13:15

)

Все таблицы кроме object_n, phones, images и object_n_site - справочники, и их объем не будет расти.

Добавлю, что в справочниках по 5-10 записей.

azesmcar

Дата 11.10.2010, 13:34 (ссылка)

(нет голосов)

Загрузка ...

uploading...

Профиль
Группа: Участник Клуба
Сообщений: 6291
Регистрация: 12.11.2004
Где: Армения

Репутация: 6
Всего: 211

нужен результат профайлера, чтобы найти проблематичные места и над этим запросом можно мозг покалечить в уме все прикидывая, можно какие нибудь тестовые данные прикрепить к теме?

Zloxa

Дата 11.10.2010, 14:20 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 33
Всего: 161

Гуру, я правильно прочитал план? я правильно понял что сканирование идет только по таблице о, остальные таблицы подтягиваются по индексу?

Цитата(Лисеночек @ 11.10.2010, 13:15

)

where o.id_supertype='39' and o.is_deleted='0' and o.id_type<>0

1) Попрбовать сделать "id_supertype=39"(убрать кавычки). Возможно индекс не используется потому что происходит неявное преобразование значения этого столбца в строку. тоже касается и is_deleted.
2) Если 1 не помогло(индекс все равно не испльзуется), принудить использовать индекс с помощью хинта.
3) Сколько записей из 71748 отбираются по этому критерию?
Может быть имеет смысл сделать составной индекс по этим трем полям, правда, тогда, помимо того что убрать кавычки в '39'и '0', пожалуй надо будет "o.id_type<>0" заменить на "(o.id_type>0[ or o.id_type<0])"

Это сообщение отредактировал(а) Zloxa - 11.10.2010, 14:23

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Лисеночек

Дата 11.10.2010, 14:45 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 32
Регистрация: 12.11.2004

Репутация: нет
Всего: нет

Цитата(Zloxa @ 11.10.2010, 14:20

)

Сколько записей из 71748 отбираются по этому критерию?

32806 записей.

Zloxa

Дата 11.10.2010, 14:59 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 33
Всего: 161

Цитата(Лисеночек @ 11.10.2010, 14:45

)

32806 записей.

Это плохо. smile

Для такой селективности приемущество использования индекса спорно.

Ну а кавычки то поубирали? Чтонибудь изменилось? Индекс попробовали использовать? Как с ним?

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Лисеночек

Дата 11.10.2010, 15:41 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 32
Регистрация: 12.11.2004

Репутация: нет
Всего: нет

Убрала кавычки, удалила индексы id_supertype и id_type и добавила составной индекс id_supertype, id_type, is_deleted. По времени не уменьшилось, explain теперь поменялся в первой строке:

Код


id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    o    ref    ind1    ind1    8    const,const    32283    Using where; Using temporary; Using filesort

Раньше было rows - 30539, теперь - 32283.

Zloxa

Дата 11.10.2010, 15:50 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 33
Всего: 161

т.е. индекс использовать не удалось.
А намекали?

Дропать индексы Вы малость поспешили. Для начала было бы правильнее добиться использования ранее существовавших индексов, имеющих наименьшу селективность.

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Лисеночек

Дата 11.10.2010, 20:37 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 32
Регистрация: 12.11.2004

Репутация: нет
Всего: нет

Цитата(Zloxa @ 11.10.2010, 15:50

)

Для начала было бы правильнее добиться использования ранее существовавших индексов, имеющих наименьшу селективность.

Как в explain узнать - какой индекс имеет наименьшую селективность?

Zloxa

Дата 12.10.2010, 08:29 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 33
Всего: 161

Лисеночек, это надо не по плану узнавать а по содержимому таблиц. Тот индекс, отбор по которому позволяет выбрать меньше данных имеет наименьшую селективность и, соответственно, предпочтительней для использования при индексном доступе.

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Zloxa

Дата 12.10.2010, 09:37 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 33
Всего: 161

Блин, я был не внимателен. smile

В исходном плане использовался индексный доступ по id_supertype.
Прошу прощения, если сбил с толку.

блина, я тут чушь несу, а меня никто не поправил.... не дело же!!! smile

Добавлено @ 09:41
еще как вариант, попытаться создать составной индекс по (id_supertype, id) чтобы попытаться избавиться от filesort. Однако я не уверен что удастся.
я так понимаю селективность id_supertype наименьшая из перечисленных в where

Это сообщение отредактировал(а) Zloxa - 12.10.2010, 09:43

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Лисеночек

Дата 13.10.2010, 10:01 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 32
Регистрация: 12.11.2004

Репутация: нет
Всего: нет

Мне стыдно признаться, я ступила, когда создавала таблицу object_n_site забыла указать auto_increment на поле id. Я ее создавала последней и в ней пока была только 1 запись, поэтому ошибка и не выявлялась. Из-за этого, хоть в запросе использовался LIMIT, в плане было видно, что идет перебор всех 30539 записей.
В общем эта проблема - только моя вина. Теперь запрос выполняется 0,4 сек.
Спасибо всем.

1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| MySQL \| Следующая тема »