Меню
Что нового?

оптимизация запроса или страницы

mak_sim2001

Новичок
оптимизация запроса или страницы

Собственно сам запрос:
Код: 
    SELECT c.Company_id
                , c.Company_name
                , c.Company_city
                , c.Company_province
                , c.Company_siccode
                , c.Company_status
                , c.Company_province
                , GROUP_CONCAT(DISTINCT a.Activity_title SEPARATOR ', ') as Activity_titles
                , GROUP_CONCAT(DISTINCT cat.Category_title SEPARATOR ', ') as Category_titles
                , GROUP_CONCAT(DISTINCT t.Type_title SEPARATOR ', ') as Type_titles
                , s.Sic_status
      FROM `Company` AS c
      LEFT JOIN `Company_Activity` AS ca
             ON c.Company_id = ca.Company_id
      LEFT JOIN `Activity` AS a
             ON ca.Activity_id = a.Activity_id
      LEFT JOIN `Activity_Category` AS ac
             ON a.Activity_id = ac.Activity_id
      LEFT JOIN `Category` AS cat
             ON ac.Category_id = cat.Category_id
      LEFT JOIN `Company_Type` AS ct
             ON c.Company_id = ct.Company_id
      LEFT JOIN `Type` AS t
             ON ct.Type_id = t.Type_id
      INNER JOIN `Sic` AS s
              ON c.Company_siccode = s.Sic_code
      WHERE 1
              GROUP BY c.Company_id
       LIMIT 0, 30
Собственно запрос выполняется суперовски быстро (уже оптимизировал как мог время выполнения в PMA: запрос занял 0.0018)
но одна пробллема мне надо сортировать эапрос по одному выбраному пользователем параметру:
c.Company_name или c.Company_city или c.Company_province запрос умирает так как сортировка не по индексу получается.
Сейчас в таблице Company около 1 000 000 строк.
Какие варианты оптимизировать запрос? страницу?
Это страница с поиском, в условиях поиска могут быть c.Company_name, c.Company_city, c.Company_province, ca.Activity_id, ac.Category_id, ct.Type_id
По поводу JOIN даже если оставить один JOIN запрос выполняетс 16-17 секунд. (если в базе 100 000 записей всё летает)
 

mak_sim2001

Новичок
Немного упростил запрос:
Код: 
SELECT c.Company_id
     , c.Company_name
     , GROUP_CONCAT( DISTINCT ca.Activity_id SEPARATOR ', ' ) AS Activity_titles
FROM `Company` AS c
LEFT JOIN `Company_Activity` AS ca ON c.Company_id = ca.Company_id
WHERE 1
GROUP BY c.Company_id
ORDER BY c.Company_name
LIMIT 0, 30

CREATE TABLE `Company` (
  `Company_id` int(11) NOT NULL auto_increment,
  `Company_name` varchar(250) NOT NULL,
  `Company_address` varchar(250) NOT NULL,
  `Company_city` varchar(50) NOT NULL,
  `Company_province` varchar(50) NOT NULL,
  `Company_postal` varchar(7) default NULL,
  `Company_phone` varchar(15) default NULL,
  `Company_fax` varchar(15) default NULL,
  `Company_email` varchar(250) default NULL,
  `Company_website` varchar(250) default NULL,
  `Company_notes` varchar(250) default NULL,
  `Company_emploers` varchar(60) NOT NULL,
  `Company_gender` enum('M','F','NA') NOT NULL,
  `Company_cname` varchar(250) NOT NULL,
  `Company_sales` varchar(50) NOT NULL,
  `Company_siccode` varchar(50) NOT NULL,
  `Company_ctitle` varchar(50) NOT NULL,
  `Company_status` enum('P','H','R') default NULL,
  `Company_active` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`Company_id`),
  KEY `Company_siccode` (`Company_siccode`),
  KEY `Company_name` (`Company_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=983275 ;

CREATE TABLE `Company_Activity` (
  `Company_id` int(11) NOT NULL,
  `Activity_id` int(11) NOT NULL,
  UNIQUE KEY `Company_id_2` (`Company_id`,`Activity_id`),
  KEY `Company_id` (`Company_id`),
  KEY `Activity_id` (`Activity_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Такой запрос выполняется 14-15sec.

-~{}~ 25.02.09 19:15:

Сейчас таблица Company занимает около 180 - 190MB

-~{}~ 25.02.09 19:28:

Это же запрос но без ORDER BY c.Company_name занимает 0.0006 сек. Как я понимаю без сортировки берётся 30 строк и таблицы и делается JOIN (не надо пробегать по 1м записей) а с сортировкой сначала делается join для 1м записей а потом сортировка.
 

tashkentchi

Новичок
Автор оригинала: mak_sim2001
Код: 
SELECT c.Company_id
     , c.Company_name
     , GROUP_CONCAT( DISTINCT ca.Activity_id SEPARATOR ', ' ) AS Activity_titles
FROM `Company` AS c
LEFT JOIN `Company_Activity` AS ca ON c.Company_id = ca.Company_id
WHERE 1
GROUP BY c.Company_id
ORDER BY c.Company_name
LIMIT 0, 30
Нажмите для раскрытия...
Попробуйте разбить этот запрос на 2:

1.
[sql]SELECT Company_id, Company_name FROM Company ORDER BY Company_name LIMIT 0, 30[/sql]
2.
[sql]SELECT GROUP_CONCAT(DISTINCT Activity_id SEPARATOR ', ') AS Activity_titles
FROM Company_Activity
WHERE Company_id IN (...)
GROUP BY Company_id[/sql]
 

mak_sim2001

Новичок
tashkentchi
не совсем понял как разбить, сделать подзапрос?
(запросе 2 к подзапросу 1) если да то не тот результат получается. (компания может не иметь activity)

я сдлал нечто похожее но юзал php

если запрос пользователя не содержит поиска по вложенным таблицам то делаю запрос только `Company` с сортировкой, а затем делаю 30 запросов к вложеным таблицам

а если поиск содержит ограничения по вложеным таблицам то юзаю мой самый первый SQL только делаю RIGHT JOIN там где это возможно(зависит от параметров поиска)
Работает корявенько страница не больше чем за 1 sec рисуется. Но это всеравно не есть good (
 

tashkentchi

Новичок
Делаете первый запрос. - Получаете 30 штук Company_id, который через зпт перечисляете в Company_id IN (. . .) второго запроса и выполняет этот второй.

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

mak_sim2001

Новичок
tashkentchi
Спасибо за помощь, сейчас все работает, а вообще на будущее какие есть способы оптимизации подобных страниц, запросов? Может имеет смысл создавать временные таблицы?

Какие требования обычно выставляются к хостингу при работе с такими объёмами данных?
 

tashkentchi

Новичок
Автор оригинала: mak_sim2001
tashkentchi
Спасибо за помощь, сейчас все работает, а вообще на будущее какие есть способы оптимизации подобных страниц, запросов? Может имеет смысл создавать временные таблицы?

Какие требования обычно выставляются к хостингу при работе с такими объёмами данных?
Нажмите для раскрытия...
Ничего особенного про хостинг сказать не могу.
Способы оптимизации: индексы, упрощение запросов (лучше 2 простых, чем один - сложный), кэширование. На счет временных таблиц - сомневаюсь. Очень помогли бы материализованные вьюхи, но их в мускуле нет. Можно придумать костыли для их замены.
 
Войдите или зарегистрируйтесь для ответа.
Сверху