CMS MADE SIMPLE FORGE

CMS Made Simple Core

 
Back Back to List

[#12506] Not optimal a database query

avatar
Created By: Yuri Haperski (cmsms)
Date Submitted: Mon Dec 20 13:30:51 -0500 2021

Assigned To: Fernando Morgado (JoMorg)
Version: 2.2.15
CMSMS Version: 2.2.15
Severity: Minor
Resolution: None
State: Open
Summary:
Not optimal a database query
Detailed Description:
I have a client's website, where table cms_module_search_items now has 3500000
records.
The databse query:
$db->Execute('DELETE FROM '.CMS_DB_PREFIX.'module_search_index WHERE item_id NOT
IN (SELECT id FROM '.CMS_DB_PREFIX.'module_search_items)');
of the function search_DeleteWords (search.tools.php) 
frizes a webserver, takes too much memory and execution time is too long.

I suggest a new redaction:

function search_DeleteWords(&$obj, $module = 'Search', $id = -1, $attr = '')
{
    $db = $obj->GetDb();

$q = "SELECT id FROM " . CMS_DB_PREFIX . 'module_search_items WHERE
module_name=?';
    $parms = array($module);

    if ($id != -1) {
        $q .= " AND content_id=?";
        $parms[] = $id;
    }
    if ($attr != '') {
        $q .= " AND extra_attr=?";
        $parms[] = $attr;
    }

    $db->BeginTrans();

    $search_items_ids = $db->GetAll($q, $parms);

    if ($db->Affected_Rows() > 1) {
$q = "DELETE FROM " . CMS_DB_PREFIX . 'module_search_items WHERE
module_name=?';
        $db->Execute($q, $parms);

        foreach ($search_items_ids as $items_id) {
            $parms = array($items_id['id']);
$db->Execute('DELETE FROM ' . CMS_DB_PREFIX . 'module_search_index
WHERE item_id = ?', $parms);
        }
    } else {
        $parms = array($search_items_ids[0]['id']);
$q = "DELETE FROM " . CMS_DB_PREFIX . 'module_search_items WHERE id =
?';
        $db->Execute($q, $parms);
$db->Execute('DELETE FROM ' . CMS_DB_PREFIX . 'module_search_index WHERE
item_id = ?', $parms);
    }

    $db->CommitTrans();
\CMSMS\HookManager::do_hook('Search::SearchItemDeleted', [$module, $id,
$attr]);
}

It works, tasted.


History

Comments
avatar 
Date: 2021-12-21 03:10
Posted By: tom (tomphantoo)

As it happens, the corresponding part of work-in-progress for the next major
CMSMS release is similarly without sub-selection :

$parms = [$modname];
$q = 'SELECT id FROM '.CMS_DB_PREFIX.'module_search_items WHERE module_name=?';
if ($id != -1) {
    $q .= ' AND content_id=?';
    $parms[] = $id;
}
if ($attr != '') {
    $q .= ' AND extra_attr=?';
    $parms[] = $attr;
}
$db = ... usual getter 
$db->BeginTrans(); // hence prefer InnoDB tables
$scrubs = $db->getCol($q, $parms);
if ($scrubs) {
    $in = '('.implode(',', $scrubs).')';
$db->execute('DELETE FROM '.CMS_DB_PREFIX.'module_search_items WHERE id IN
'.$in);
$db->execute('DELETE FROM '.CMS_DB_PREFIX.'module_search_index WHERE item_id
IN '.$in);
}
$db->CommitTrans();

Never tried with a large table, though
avatar 
Date: 2021-12-27 20:52
Posted By: tom (tomphantoo)

As it happens, the corresponding part of work-in-progress for the next major
CMSMS release is similarly without sub-selection :

$parms = [$modname];
$q = 'SELECT id FROM '.CMS_DB_PREFIX.'module_search_items WHERE module_name=?';
if ($id != -1) {
    $q .= ' AND content_id=?';
    $parms[] = $id;
}
if ($attr != '') {
    $q .= ' AND extra_attr=?';
    $parms[] = $attr;
}
$db = ... usual getter 
$db->BeginTrans(); // hence prefer InnoDB tables
$scrubs = $db->getCol($q, $parms);
if ($scrubs) {
    $in = '('.implode(',', $scrubs).')';
$db->execute('DELETE FROM '.CMS_DB_PREFIX.'module_search_items WHERE id IN
'.$in);
$db->execute('DELETE FROM '.CMS_DB_PREFIX.'module_search_index WHERE item_id
IN '.$in);
}
$db->CommitTrans();

Never tried with a large table, though
avatar 
Date: 2022-01-08 05:50
Posted By: Yuri Haperski (cmsms)

Your code works fine with a large database.
Replace $parms = [$modname]; with $parms = [$module];
Updates

Updated: 2021-12-21 03:10
resolution_id: => 5