Osclass Search - important SQL query optimization

[dev-101]

Few months ago I was investing something interesting about performance and potential search optimization, and the whole reason for that is this topic by Syed from the forum:

https://forums.osclass.org/general-help/osclass-3-8-0-rc-feels-like-helping/msg163854/#msg163854
https://forums.osclass.org/general-help/latest-items-with-photos-slows-down-the-load-time-of-my-home-page/

Long story short, I found help from guys at EverSQL and especially their CEO and co-founder Tomer Shay. With his help, the slow query was optimized and speed-up by a factor x2 or x3 in some cases.

[dev-101]

This is my reply / excerpt from the forum:

With my similar laptop configuration (4 cores, 4 GB RAM etc.) and localhost (not live server), and no extra plugins, I got similar times for sql part:

~34k items | ~0.6s for /search/ | ~1s for /search/ with pictures | ~0.7s main page with Bender's default + your code

The slowest queries are this one in all cases:

--> QUERY TIME 0.33451795578003
**************************************************
SELECT oc_t_item.pk_i_id
FROM (oc_t_item)
WHERE oc_t_item.b_enabled = 1 AND oc_t_item.b_active = 1 AND oc_t_item.b_spam = 0 AND (oc_t_item.b_premium = 1 || oc_t_item.dt_expiration >= '2018-07-24 02:54:26')
ORDER BY dt_pub_date desc
LIMIT 1200

--> QUERY TIME 0.6087589263916
**************************************************
SELECT oc_t_item.*, oc_t_item.s_contact_name as s_user_name
FROM (oc_t_item)
LEFT  JOIN oc_t_item_resource ON oc_t_item_resource.fk_i_item_id = oc_t_item.pk_i_id
WHERE oc_t_item.b_enabled = 1 AND oc_t_item.b_active = 1 AND oc_t_item.b_spam = 0 AND (oc_t_item.b_premium = 1 || oc_t_item.dt_expiration >= '2018-07-24 02:54:36')
AND oc_t_item_resource.s_content_type LIKE '%image%' 
GROUP BY oc_t_item.pk_i_id
ORDER BY dt_pub_date desc
LIMIT 0, 12

--------------------------------------------------
--> QUERY TIME 0.15021014213562
**************************************************
SELECT oc_t_item.pk_i_id
FROM (oc_t_item)
LEFT  JOIN oc_t_item_resource ON oc_t_item_resource.fk_i_item_id = oc_t_item.pk_i_id
WHERE oc_t_item.b_enabled = 1 AND oc_t_item.b_active = 1 AND oc_t_item.b_spam = 0 AND (oc_t_item.b_premium = 1 || oc_t_item.dt_expiration >= '2018-07-24 02:54:36')
AND oc_t_item_resource.s_content_type LIKE '%image%' 
GROUP BY oc_t_item.pk_i_id
ORDER BY dt_pub_date desc
LIMIT 1200

This is what takes the most of the time in search pages & latest items w/wo photos.

[dev-101]

Now, back to my work with EverSQL, we focused on this one (slowest case) -- this is the original non-optimized query:

SELECT oc_t_item.*, oc_t_item.s_contact_name as s_user_name
FROM (oc_t_item)
LEFT  JOIN oc_t_item_resource ON oc_t_item_resource.fk_i_item_id = oc_t_item.pk_i_id
WHERE oc_t_item.b_enabled = 1 AND oc_t_item.b_active = 1 AND oc_t_item.b_spam = 0 AND (oc_t_item.b_premium = 1 || oc_t_item.dt_expiration >= '2018-07-24 02:54:36')
AND oc_t_item_resource.s_content_type LIKE '%image%' 
GROUP BY oc_t_item.pk_i_id
ORDER BY dt_pub_date desc
LIMIT 0, 12

And this is what EverSQL come up with:

modify table / add index:

	ALTER TABLE `oc_t_item` ADD INDEX `oc_t_item_idx_dt_pub_date` (`dt_pub_date`);

optimized query:

SELECT straight_join oc_t_item.*, oc_t_item.s_contact_name as s_user_name
FROM (oc_t_item )
INNER JOIN oc_t_item_resource ON oc_t_item_resource.fk_i_item_id = oc_t_item.pk_i_id
WHERE oc_t_item.b_enabled = 1 AND oc_t_item.b_active = 1 AND oc_t_item.b_spam = 0 AND (oc_t_item.b_premium = 1 OR oc_t_item.dt_expiration >= '2018-07-24 02:54:36')
AND oc_t_item_resource.s_content_type LIKE '%image%'
GROUP BY dt_pub_date, oc_t_item.pk_i_id
ORDER BY dt_pub_date desc , oc_t_item.pk_i_id desc
LIMIT 0, 12;

Later, we concluded that straight_join can be omitted, thus avoiding potential problems. Speed gain still applies.

I have sent to Osclass Team my test database and results, reduction in exec time is obvious (down from 0.6-0.7 seconds to under 0.3 seconds).

We should consider applying this changes in the DAO class and database.

[dev-101]

comparison view:

[Adyyda]

Hi. Have these been implemented in latest revision of Osclass? I tried today to access Osclass site and is not working? What is happening? Market was closed and now the site is down? Is this the end?