Оптимизация запроса

Тема в разделе "Базы данных", создана пользователем dmsoh, 26 апр 2009.

Статус темы:
Закрыта.
Модераторы: latteo
  1. dmsoh

    dmsoh

    Регистр.:
    27 янв 2007
    Сообщения:
    192
    Симпатии:
    42
    Есть сайт с объявлениями, больше 5000. На каждой страничке списка объявлений (1,2,3...) примерно 50 запросов к базе данных MySql. Время генерации страницы очень большое до 14 секунд + время загрузки примерно 3 сек, итого сайт очень тормозит, хотя работает на хорошем VPS.

    При этом, если 49 запросов занимают доли секунд, то один - 70 % всего времени генерации - запрос на выборку списка объявлений. Почему так происходит я не знаю, ведь выборка ограничивается 10-ю результатами.

    Вот сам запрос:

    Код:
    
    (11.4073410034 sec)
    
    SELECT a.*, a.mileage*(1 + a.mileage_unit*0.609) as metric_mileage, a.mileage*(1 - (1 - a.mileage_unit)*0.3785) as english_mileage,
     p.id as photo_id, count(p.id) as photo_count, v.itemid as vendor_itemid, v.title as vendortitle, m.itemid as model_itemid, m.title as modeltitle, c.title as colortitle,
     cur.sign as currency_sign, a.price*cur.rate as price_in_rur, DATE_FORMAT(a.createDate, '%a, %d %b %Y %T GMT') as rfcDate, ct.title as city_title, r.title as region_title
     FROM jos_autobb_messages AS a
     LEFT JOIN jos_autobb_photos AS p
     ON a.id=p.msgid
     LEFT JOIN jos_autobb_vendors AS v
     ON a.vendor=v.id
     LEFT JOIN jos_autobb_models AS m
     ON a.model=m.id
     LEFT JOIN jos_autobb_colors AS c
     ON a.color=c.id
     LEFT JOIN jos_autobb_currency AS cur
     ON a.currency=cur.id
     LEFT JOIN jos_autobb_cities AS ct
     ON a.city=ct.id
     LEFT JOIN jos_autobb_regions AS r
     ON ct.region=r.id
     WHERE a.expirationDate>=NOW() AND a.published=1
     GROUP BY a.id
     ORDER BY a.sticked DESC, a.ordering ASC, a.CreateDate DESC
     LIMIT 0, 10
    Посоветуйте, как быть?
     
  2. PHP_Master

    PHP_Master

    Регистр.:
    3 фев 2008
    Сообщения:
    2.647
    Симпатии:
    590
    У тебя сами база и скрипт спроектированы через одно место, если для такой банальщины
    как "запрос на выборку списка объявленийs необходимо объединять 8 таблиц.
     
  3. dmsoh

    dmsoh

    Регистр.:
    27 янв 2007
    Сообщения:
    192
    Симпатии:
    42
    Понимаю, но как говориться что имеем, то имеем. 8 таблиц - это и таблица городов, и марок и моделей, и фотографий и т.п. и без них никак не обойтись. Это все в списке нужно отобразить.

    Но может подскажите, как можно облегчить нагрузку?
     
  4. MadWizard

    MadWizard Постоялец

    Регистр.:
    24 мар 2009
    Сообщения:
    145
    Симпатии:
    20
    Без структуры таблиц трудно ставить диагноз.

    Навскидку, проверь, чтобы на всех полях условий и сортировки стояли индексы: a.expirationDate, a.published, a.id, a.sticked, a.ordering, a.CreateDate.
    Следующий шаг - индексы на полях, по которым джойнишь.
     
    dmsoh нравится это.
  5. dmsoh

    dmsoh

    Регистр.:
    27 янв 2007
    Сообщения:
    192
    Симпатии:
    42
    Спасибо за совет, индексы добавлю.

    Вот структура таблиц

    PHP:

            CREATE TABLE 
    IF NOT EXISTS `#__autobb_bodies` (
              `
    idint(11NOT NULL auto_increment,
              `
    categoryint(11NOT NULL default '0',
              `
    titlevarchar(100CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              
    PRIMARY KEY  (`id`)
            ) 
    Type=MyISAM  DEFAULT CHARSET=cp1251


            
    CREATE TABLE IF NOT EXISTS `#__autobb_cities` (
              `
    idint(11NOT NULL auto_increment,
              `
    titlevarchar(255CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              `
    english_titlevarchar(255CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              `
    regionint(11NOT NULL default '0',
              `
    ismaintinyint(1NOT NULL default '0',
              `
    metrotinyint(1NOT NULL default '0',
              `
    prefixvarchar(100CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              `
    gismeteo_codevarchar(100CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              `
    orderingint(11NOT NULL default '0',
              
    PRIMARY KEY  (`id`)
            ) 
    Type=MyISAM  DEFAULT CHARSET=cp1251


            
    CREATE TABLE IF NOT EXISTS `#__autobb_colors` (
              `
    idint(11NOT NULL auto_increment,
              `
    titlevarchar(100CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              `
    orderingint(11NOT NULL default '0',
              
    PRIMARY KEY  (`id`)
            ) 
    Type=MyISAM  DEFAULT CHARSET=cp1251
        
        
            
    CREATE TABLE IF NOT EXISTS `#__autobb_complectations` (
              `
    idint(11NOT NULL auto_increment,
              `
    categoryint(11NOT NULL default '0',
              `
    titlevarchar(100CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              `
    optionsvarchar(255CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              
    PRIMARY KEY  (`id`)
            ) 
    Type=MyISAM  DEFAULT CHARSET=cp1251
         
         
            
    CREATE TABLE `#__autobb_config` (
      `
    idint(11NOT NULL auto_increment,
      `
    categoryint(11NOT NULL default '0',
      `
    show_eulatinyint(1NOT NULL default '0',
      `
    eulatext NOT NULL,
      `
    addadstinyint(1NOT NULL default '1',
      `
    allow_guestposttinyint(1NOT NULL default '0',
      `
    autopublishtinyint(1NOT NULL default '0',
      `
    price_listvarchar(255NOT NULL default '',
      `
    mileage_listvarchar(255NOT NULL default '',
      `
    volume_listvarchar(255NOT NULL default '',
      `
    city_popup_widthint(11NOT NULL default '0',
      `
    city_popup_heightint(11NOT NULL default '0',
      `
    default_mileage_unittinyint(1NOT NULL default '0',
      `
    default_speed_unittinyint(1NOT NULL default '0',
      `
    antifloodint(11NOT NULL default '3600',
      `
    allow_jcommentstinyint(1NOT NULL default '0',
      `
    allow_csstinyint(1NOT NULL default '0',
      `
    css_sfxvarchar(100NOT NULL default '',
      `
    show_info_ownertinyint(1NOT NULL default '0',
      `
    state_newtinyint(3NOT NULL default '10',
      `
    vendor_description_showint(11NOT NULL default '0',
      `
    model_description_showint(11NOT NULL default '0',
      `
    write_accessint(1NOT NULL default '0',
      `
    lang_sfxvarchar(100NOT NULL default '',
      `
    allow_search_complektstinyint(1NOT NULL default '0',
      `
    extsearch_lifetimeint(11NOT NULL default '0',
      `
    color_sorttinyint(1NOT NULL default '0',
      `
    foto_show_sorttinyint(1NOT NULL default '0',
      `
    defaultDuringint(11NOT NULL default '0',
      `
    allow_statstinyint(1NOT NULL default '0',
      `
    photos_maxnumint(11NOT NULL default '0',
      `
    photos_maxsizeint(11NOT NULL default '0',
      `
    photos_maxwidthint(11NOT NULL default '0',
      `
    photos_maxheightint(11NOT NULL default '0',
      `
    photos_qualityint(11NOT NULL default '0',
      `
    photos_savequalityint(11NOT NULL default '0',
      `
    thumb_maxsizeint(11NOT NULL default '0',
      `
    thumb_qualityint(11NOT NULL default '0',
      `
    middle_maxsizeint(11NOT NULL default '0',
      `
    middle_qualityint(11NOT NULL default '0',
      `
    custom_maxsizeint(11NOT NULL default '0',
      `
    custom_qualityint(11NOT NULL default '0',
      `
    put_logotinyint(1NOT NULL default '0',
      `
    logo_positionint(11NOT NULL default '0',
      `
    logo_sfxvarchar(100NOT NULL default '',
      `
    allow_fullsizetinyint(1NOT NULL default '0',
      `
    photos_allow_reloadtinyint(1NOT NULL default '0',
      `
    photos_antialiastinyint(1NOT NULL default '0',
      `
    year_maxnumint(11NOT NULL default '0',
      `
    cachetime_showint(11NOT NULL default '0',
      `
    cachetime_photoint(11NOT NULL default '0',
      `
    show_nametinyint(1NOT NULL default '0',
      `
    show_companytinyint(1NOT NULL default '0',
      `
    show_additionaltinyint(1NOT NULL default '0',
      `
    show_emailtinyint(1NOT NULL default '0',
      `
    show_phone1tinyint(1NOT NULL default '0',
      `
    show_phone2tinyint(1NOT NULL default '0',
      `
    show_phone3tinyint(1NOT NULL default '0',
      `
    show_citytinyint(1NOT NULL default '0',
      `
    show_pricetinyint(1NOT NULL default '0',
      `
    show_modificationtinyint(1NOT NULL default '0',
      `
    show_colortinyint(1NOT NULL default '0',
      `
    show_yeartinyint(1NOT NULL default '0',
      `
    show_bodytypetinyint(1NOT NULL default '0',
      `
    show_enginetinyint(1NOT NULL default '0',
      `
    show_fuelinputcitytinyint(1NOT NULL default '0',
      `
    show_fuelinputcountrytinyint(1NOT NULL default '0',
      `
    show_fuelinputbothtinyint(1NOT NULL default '0',
      `
    show_mileagetinyint(1NOT NULL default '0',
      `
    show_drivetinyint(1NOT NULL default '0',
      `
    show_volumetinyint(1NOT NULL default '0',
      `
    show_powertinyint(1NOT NULL default '0',
      `
    show_speedtinyint(1NOT NULL default '1',
      `
    show_transmissiontinyint(1NOT NULL default '0',
      `
    show_statetinyint(1NOT NULL default '0',
      `
    show_wrangletinyint(1NOT NULL default '0',
      `
    show_steering_wheeltinyint(1NOT NULL default '0',
      `
    show_vintinyint(1NOT NULL default '0',
      `
    show_customstinyint(1NOT NULL default '0',
      `
    show_photostinyint(1NOT NULL default '0',
      `
    show_complectationtinyint(1NOT NULL default '0',
      `
    show_createdatetinyint(1NOT NULL default '0',
      `
    show_expirationdatetinyint(1NOT NULL default '0',
      `
    seo_wordstext NOT NULL,
      `
    cachetime_seomapint(11NOT NULL default '2592000',
      `
    allow_rsstinyint(1NOT NULL default '0',
      `
    rss_titlevarchar(255NOT NULL default '',
      `
    rss_descriptionvarchar(255NOT NULL default '',
      `
    rss_managingEditorvarchar(255NOT NULL default '',
      `
    rss_webMastervarchar(255NOT NULL default '',
      `
    rss_image_urlvarchar(255NOT NULL default '',
      `
    rss_image_titlevarchar(255NOT NULL default '',
      `
    show_link_addtinyint(1NOT NULL default '1',
      `
    show_link_searchtinyint(1NOT NULL default '1',
      `
    show_link_datetinyint(1NOT NULL default '1',
      `
    show_link_miltinyint(1NOT NULL default '1',
      `
    show_link_newtinyint(1NOT NULL default '1',
      `
    show_city_listtinyint(1NOT NULL default '1',
      `
    show_date_pubtinyint(1NOT NULL default '1',
      `
    show_search_formtinyint(1NOT NULL default '1',
      `
    show_viewstinyint(1NOT NULL default '1',
      `
    show_statustinyint(1NOT NULL default '0',
      `
    revcatstinyint(1NOT NULL default '1',
      `
    revc_cat_corint(11NOT NULL default '601',
      
    PRIMARY KEY  (`id`)
    Type=MyISAM  DEFAULT CHARSET=cp1251;
         
         
            
    CREATE TABLE IF NOT EXISTS `#__autobb_currency` (
              `
    idint(11NOT NULL auto_increment,
              `
    titlevarchar(100CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              `
    signvarchar(100CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              `
    ratefloat NOT NULL default '0',
              
    PRIMARY KEY  (`id`)
            ) 
    Type=MyISAM  DEFAULT CHARSET=cp1251
         
         
            
    CREATE TABLE IF NOT EXISTS `#__autobb_extsearch` (
              `
    idint(11NOT NULL auto_increment,
              `
    resultstext CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL,
              `
    searchParamtext CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL,
              `
    expirationDatedatetime NOT NULL default '0000-00-00 00:00:00',
              
    PRIMARY KEY  (`id`)
            ) 
    Type=MyISAM  DEFAULT CHARSET=cp1251
          
          
            
    CREATE TABLE `#__autobb_messages` (
      `
    idint(11NOT NULL auto_increment,
      `
    createDatedatetime NOT NULL default '0000-00-00 00:00:00',
      `
    modifyDatedatetime NOT NULL default '0000-00-00 00:00:00',
      `
    expirationDatedatetime NOT NULL default '0000-00-00 00:00:00',
      `
    stickedtinyint(1NOT NULL default '0',
      `
    orderingint(11NOT NULL default '0',
      `
    fronttinyint(1NOT NULL default '0',
      `
    publishedtinyint(1NOT NULL default '1',
      `
    namevarchar(100NOT NULL default '',
      `
    companyvarchar(100NOT NULL default '',
      `
    phone1varchar(100NOT NULL default '',
      `
    since1int(11NOT NULL default '0',
      `
    till1int(11NOT NULL default '0',
      `
    phone2varchar(100NOT NULL default '',
      `
    since2int(11NOT NULL default '0',
      `
    till2int(11NOT NULL default '0',
      `
    phone3varchar(100NOT NULL default '',
      `
    since3int(11NOT NULL default '0',
      `
    till3int(11NOT NULL default '0',
      `
    emailvarchar(255NOT NULL default '',
      `
    passwordvarchar(100NOT NULL default '',
      `
    cityint(11NOT NULL default '0',
      `
    useridint(11NOT NULL default '0',
      `
    categoryint(11NOT NULL default '0',
      `
    vendorint(11NOT NULL default '0',
      `
    modelint(11NOT NULL default '0',
      `
    modificationvarchar(100NOT NULL default '',
      `
    colorint(11NOT NULL default '0',
      `
    yearint(11NOT NULL default '0',
      `
    bodytypeint(11NOT NULL default '0',
      `
    priceint(11NOT NULL default '0',
      `
    currencyint(11NOT NULL default '0',
      `
    engineint(11NOT NULL default '0',
      `
    fuelinputcitydecimal(4,1NOT NULL default '0.0',
      `
    fuelinputcountrydecimal(4,1NOT NULL default '0.0',
      `
    fuelinputbothdecimal(4,1NOT NULL default '0.0',
      `
    mileageint(11NOT NULL default '0',
      `
    mileage_unitint(11NOT NULL default '0',
      `
    driveint(11NOT NULL default '0',
      `
    volumeint(11NOT NULL default '0',
      `
    powerint(11NOT NULL default '0',
      `
    speedint(3NOT NULL default '0',
      `
    transmissionint(11NOT NULL default '0',
      `
    stateint(11NOT NULL default '0',
      `
    steering_wheeltinyint(1NOT NULL default '0',
      `
    wrangletinyint(1NOT NULL default '0',
      `
    vinvarchar(100NOT NULL default '',
      `
    vin2varchar(100NOT NULL default '',
      `
    customstinyint(1NOT NULL default '0',
      `
    additionaltext,
      `
    viewsint(11NOT NULL default '0',
      `
    entinyint(1NOT NULL default '0',
      `
    crtinyint(1NOT NULL default '0',
      `
    lastViewIPvarchar(20) default NULL,
      `
    ipvarchar(20) default NULL,
      `
    bannedtinyint(1NOT NULL default '0',
      
    PRIMARY KEY  (`id`),
      
    KEY `createDate` (`createDate`),
      
    KEY `expirationDate` (`expirationDate`),
      
    KEY `vendor` (`vendor`),
      
    KEY `model` (`model`),
      
    KEY `city` (`city`),
      
    KEY `state` (`state`)
    Type=MyISAM  DEFAULT CHARSET=cp1251;
        
        
            
    CREATE TABLE IF NOT EXISTS `#__autobb_models` (
              `
    idint(11NOT NULL auto_increment,
              `
    titlevarchar(100CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              `
    vendorint(11NOT NULL default '0',
              `
    itemidint(11NOT NULL default '0',
              `
    descriptiontext CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL,
              
    PRIMARY KEY  (`id`)
            ) 
    Type=MyISAM  DEFAULT CHARSET=cp1251

            
    CREATE TABLE IF NOT EXISTS `#__autobb_msg_complect` (
              `
    idint(11NOT NULL auto_increment,
              `
    msgidint(11NOT NULL default '0',
              `
    complectidint(11NOT NULL default '0',
              `
    optionsint(11NOT NULL default '0',
              
    PRIMARY KEY  (`id`)
            ) 
    Type=MyISAM  DEFAULT CHARSET=cp1251
         
         
            
    CREATE TABLE IF NOT EXISTS `#__autobb_photos` (
              `
    idint(11NOT NULL auto_increment,
              `
    titlevarchar(100CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              `
    msgidint(11NOT NULL default '0',
              
    PRIMARY KEY  (`id`)
            ) 
    Type=MyISAM  DEFAULT CHARSET=cp1251


            
    CREATE TABLE IF NOT EXISTS `#__autobb_regions` (
              `
    idint(11NOT NULL auto_increment,
              `
    titlevarchar(255CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              
    PRIMARY KEY  (`id`)
            ) 
    Type=MyISAM  DEFAULT CHARSET=cp1251
        
        
            
    CREATE TABLE IF NOT EXISTS `#__autobb_states` (
              `
    idint(11NOT NULL auto_increment,
              `
    titlevarchar(100CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              `
    orderingint(11NOT NULL default '0',
              
    PRIMARY KEY  (`id`)
            ) 
    Type=MyISAM  DEFAULT CHARSET=cp1251

            
    CREATE TABLE IF NOT EXISTS `#__autobb_vendors` (
              `
    idint(11NOT NULL auto_increment,
              `
    categoryint(11NOT NULL default '0',
              `
    titlevarchar(100CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL default '',
              `
    itemidint(11NOT NULL default '0',
              `
    descriptiontext CHARACTER SET cp1251 COLLATE cp1251_general_ci NOT NULL,
              
    PRIMARY KEY  (`id`)
            ) 
    Type=MyISAM  DEFAULT CHARSET=cp1251
     
  6. MadWizard

    MadWizard Постоялец

    Регистр.:
    24 мар 2009
    Сообщения:
    145
    Симпатии:
    20
    Так и есть. Нужно добавить индексы на поля color, currency, published, sticked, ordering, cities.region, photos.msgid.

    Если других запросов не предполагается, для messages можно сделать один составной индекс из этих полей.
     
  7. dmsoh

    dmsoh

    Регистр.:
    27 янв 2007
    Сообщения:
    192
    Симпатии:
    42
    Последовал вашему совету.

    Это немного помогло. Т.е. Вместо 11 сек в этом запросе, уже можно видеть 4-5 сек. Общее время генерации уменьшилось. 8-9 сек, однако все-равно тяжело.
     
  8. PHP_Master

    PHP_Master

    Регистр.:
    3 фев 2008
    Сообщения:
    2.647
    Симпатии:
    590
    Чудес не бывает - только в сказке.
    Хочешь нормальной скорости - меняй структуру.
    Джоин 8-ми таблиц для вывода объявлений - это конкретный перебор.
     
  9. Simpson

    Simpson

    Регистр.:
    22 июл 2007
    Сообщения:
    373
    Симпатии:
    36
    а мне кажется, что тут проблема:
    GROUP BY
    ORDER BY

    что explain каже?
     
  10. MadWizard

    MadWizard Постоялец

    Регистр.:
    24 мар 2009
    Сообщения:
    145
    Симпатии:
    20
    Тогда нужно определить, где главные тормоза: при выборке, джойне, группировке или сортировке.
    Сначала сделать SELECT a.id FROM ..... чтобы понять, что это тормозят не вычисления в селекте.
    Потом по очереди убирать джойны, группировки, сортировки и смотреть, где будет скачок производительности. Т.е. убрать последний джойн, потом группировку, потом сортировку; убрать предпоследний джойн и т.д.

    Когда время резко скакнет, будет видно какая таблица тормозит.
     
Статус темы:
Закрыта.