Új hozzászólás Aktív témák

• #2907 pittbaba aktív tag

  Új Válasz 2015-10-18 18:54:50 #2907

  Új hozzászólás

  Összes hozzászólása itt Válaszok az összes hozzászólására itt Válaszok erre a hozzászólásra

  Privát üzenet küldése

  

  pittbaba

  aktív tag

  Sziasztok! Ritkán jövök egy egy brutál kérdéssel, hátha most is belöktök a jó irányba.
  Weblap, termékek feltöltése. Minden kategóriának és alkategóriának saját custom paraméterei ami felvihető a termékhez (monitornál átmérő, autónál köbcenti stb).
  Mivel végtelen nagy táblát kapnék, ezeket a custom adatokat külön táblákban tárolom.
  Ezeket ki kell nyerjem, így egy kérés már 5mp.

  Így néz ki a lekérés:
  SELECT SQL_CALC_FOUND_ROWS
  `apro_ads`.id AS ad_id,
  `apro_ads`.*,
  `apro_users`.*,
  `apro_cat_level1`.*,
  `apro_cat_level2`.*,
  `apro_cat_level2`.*,
  `apro_ad_favorites`.advert_id AS is_favorite,
  `apro_ad_favorites`.user_id AS favorite_user_id,
  transactions.id AS trans_id,transactions.upgrade_type,transactions.auto_top,transactions.upgrade_duration,transactions.payment_status,transactions.payed_time,transactions.ad_showed
  
  ,customs_maganszemely.element_value,customs_kinal_keres_kiadna_berelne.element_value,customs_szobak_szama.element_value,customs_meret.element_value,customs_allapot.element_value,customs_futes.element_value,customs_emelet.element_value,customs_lift.element_value,customs_ingatlan_tipusa.element_value,customs_kilatas.element_value,customs_erkely.element_value,customs_butorozott.element_value,customs_parkolas.element_value,customs_energiatanusitvany.element_value
  FROM apro_ads
  JOIN `apro_users` ON apro_ads.owner_id = `apro_users`.id
  LEFT JOIN `apro_cat_level1` ON apro_ads.cat_level1 = `apro_cat_level1`.id
  LEFT JOIN `apro_cat_level2` ON apro_ads.cat_level2 = `apro_cat_level2`.id
  LEFT JOIN `apro_cat_level3` ON apro_ads.cat_level3 = `apro_cat_level3`.id
  LEFT JOIN `apro_ad_favorites` ON apro_ads.id = `apro_ad_favorites`.advert_id
  LEFT JOIN (SELECT ad_id AS max_ad_id,MAX( id ) AS max_trans_id FROM `apro_transactions` GROUP BY max_ad_id) AS max_trans_id ON max_trans_id.max_ad_id = apro_ads.id
  LEFT JOIN `apro_transactions` AS transactions ON (transactions.id = max_trans_id.max_trans_id)
  
  LEFT JOIN ( SELECT element_value, item_id FROM `apro_category_customs` WHERE element_name = 'customs_maganszemely') AS customs_maganszemely ON `apro_ads`.id = customs_maganszemely.item_id
  LEFT JOIN ( SELECT element_value, item_id FROM `apro_category_customs` WHERE element_name = 'customs_kinal_keres_kiadna_berelne') AS customs_kinal_keres_kiadna_berelne ON `apro_ads`.id = customs_kinal_keres_kiadna_berelne.item_id
  LEFT JOIN ( SELECT element_value, item_id FROM `apro_category_customs` WHERE element_name = 'customs_szobak_szama') AS customs_szobak_szama ON `apro_ads`.id = customs_szobak_szama.item_id
  LEFT JOIN ( SELECT element_value, item_id FROM `apro_category_customs` WHERE element_name = 'customs_meret') AS customs_meret ON `apro_ads`.id = customs_meret.item_id
  LEFT JOIN ( SELECT element_value, item_id FROM `apro_category_customs` WHERE element_name = 'customs_allapot') AS customs_allapot ON `apro_ads`.id = customs_allapot.item_id
  LEFT JOIN ( SELECT element_value, item_id FROM `apro_category_customs` WHERE element_name = 'customs_futes') AS customs_futes ON `apro_ads`.id = customs_futes.item_id
  LEFT JOIN ( SELECT element_value, item_id FROM `apro_category_customs` WHERE element_name = 'customs_emelet') AS customs_emelet ON `apro_ads`.id = customs_emelet.item_id
  LEFT JOIN ( SELECT element_value, item_id FROM `apro_category_customs` WHERE element_name = 'customs_lift') AS customs_lift ON `apro_ads`.id = customs_lift.item_id
  LEFT JOIN ( SELECT element_value, item_id FROM `apro_category_customs` WHERE element_name = 'customs_ingatlan_tipusa') AS customs_ingatlan_tipusa ON `apro_ads`.id = customs_ingatlan_tipusa.item_id
  LEFT JOIN ( SELECT element_value, item_id FROM `apro_category_customs` WHERE element_name = 'customs_kilatas') AS customs_kilatas ON `apro_ads`.id = customs_kilatas.item_id
  LEFT JOIN ( SELECT element_value, item_id FROM `apro_category_customs` WHERE element_name = 'customs_erkely') AS customs_erkely ON `apro_ads`.id = customs_erkely.item_id
  LEFT JOIN ( SELECT element_value, item_id FROM `apro_category_customs` WHERE element_name = 'customs_butorozott') AS customs_butorozott ON `apro_ads`.id = customs_butorozott.item_id
  LEFT JOIN ( SELECT element_value, item_id FROM `apro_category_customs` WHERE element_name = 'customs_parkolas') AS customs_parkolas ON `apro_ads`.id = customs_parkolas.item_id
  LEFT JOIN ( SELECT element_value, item_id FROM `apro_category_customs` WHERE element_name = 'customs_energiatanusitvany') AS customs_energiatanusitvany ON `apro_ads`.id = customs_energiatanusitvany.item_id
  WHERE `apro_ads`.`cat_level1` = 2 AND `apro_ads`.`cat_level2` = 6 AND a_town ='Budapest' AND `customs_maganszemely`.`element_value` = '1' AND `customs_kinal_keres_kiadna_berelne`.`element_value` = '1' AND a_status='A'
  AND transactions.payment_status = 'payed'
  AND DATE_ADD(transactions.`payed_time`,INTERVAL transactions.`upgrade_duration` DAY) >= NOW()
  AND a_status='A'
  AND `apro_users`.status = 'A'
  GROUP BY ad_id
  ORDER BY transactions.ad_showed ASC

  Csatoltam egy explain kimenetet, hogy lássátok mi van indexelve.
  [link]

Új hozzászólás Aktív témák