Sir look at the query execution time more than 10 second when you have a lot of categories and attributes

10.220079898834    SELECT DISTINCT a.*
FROM (oc_zw_t_attribute as a)
LEFT OUTER JOIN oc_zw_t_item_attribute as i ON (a.pk_i_id = i.fk_i_attribute_id AND (i.fk_i_item_id = 66384491 OR a.s_type = "DIVIDER"))
WHERE a.b_enabled = 1
AND ((concat(concat(",", a.s_category_id), ",") like "%,888,%") OR (concat(concat(",", a.s_category_id), ",") like "%,916,%") OR a.s_category_id is null OR a.s_category_id = "")
ORDER BY a.i_order ASC, a.pk_i_id ASC


You are storing categories id in a fied value and doing a full table scan, this is an expensive query sir. You need a table for mapping category and attibutes.

Same for attributes and Car attributes pro.

Can you fix this ASAP, you can't do this kind of coding. Thanks

Osclass should not have more than 500 categories.
Create supportive indexes in your database.

When you have 80m ads in the system every optimization is important.

For instance here you are doing an outer join to a big table attribute_item with 180m rows as many items have more attributes. I have put a sub query to only do outer join for the relevant item id. etc. (execution from 20seconds to 0.0001 seconds)

You need to shift the focus from small classified ads with 100-500 items on the system to big one with millions and rework your queries for speed.


public function getItemAttributes($item_id, $category_id = NULL) {
  $cache_key = md5(osc_base_url() . 'Plugin::Attributes::getItemAttributes::' . (string)$item_id . (string)$category_id);
  $found = null;
  $cache = osc_cache_get($cache_key, $found);

  if(atr_param('enable_cache') == 1 && $cache !== false && OC_ADMIN === false) {
    return $cache;
  } else {
    $this->dao->select('DISTINCT a.*');
    //$this->dao->from($this->getTable_attribute_item() . ' as i, ' . $this->getTable_attribute() . ' as a');
    $this->dao->from($this->getTable_attribute() . ' as a');
   
    //$this->dao->join( $this->getTable_attribute_item() . ' as i', '(a.pk_i_id = i.fk_i_attribute_id AND ((i.fk_i_item_id = ' . $item_id . ' AND (i.fk_i_attribute_value_id > 0 OR trim(s_value) <> "")) OR a.s_type = "DIVIDER"))', 'LEFT OUTER');
   
    // Subquery for the LEFT OUTER JOIN on oc_zw_t_item_attribute
    $this->dao->join(
        '(SELECT fk_i_attribute_id FROM ' . $this->getTable_attribute_item() . ' WHERE fk_i_item_id = ' . $item_id . ') as i',
        'a.pk_i_id = i.fk_i_attribute_id',
        'LEFT OUTER'
    );

    //$this->dao->join( $this->getTable_attribute_item() . ' as i', '(a.pk_i_id = i.fk_i_attribute_id AND (i.fk_i_item_id = ' . $item_id . ' OR a.s_type = "DIVIDER"))', 'LEFT OUTER');

    //$this->dao->where('i.fk_i_item_id', $item_id);
    //$this->dao->where('a.pk_i_id = i.fk_i_attribute_id');
    $this->dao->where('a.b_enabled = 1');
    $this->dao->orderby('a.i_order ASC, a.pk_i_id ASC');

@Cecile
If you have 80m of ads, you do not ask people on forums to help you, instead you customize solutions for your needs.