Skip to content

Vtiger 7.4 global search is inefficient and takes too long on large DBs

When doing a global search with an equivalent database, Vtiger 6.5 takes 2 seconds, while vtiger 7.4 takes about 1 minute. That's because:

Vtiger 6 does this in Vtiger_BasicAjax_View:

image

Which produces a  single query:

[25-Feb-2022 11:15:05 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 con params Array
(
    [0] => %prueba%
)

While vtiger 7 does this in Vtiger_ListAjax_View:

image

Which produces the following queries (see how every query takes about 2-3 seconda):

[25-Feb-2022 11:09:21 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Potentials
)

[25-Feb-2022 11:09:23 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Contacts
)

[25-Feb-2022 11:09:25 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Accounts
)

[25-Feb-2022 11:09:26 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Leads
)

[25-Feb-2022 11:09:28 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Documents
)

[25-Feb-2022 11:09:30 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Calendar
)

[25-Feb-2022 11:09:31 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => HelpDesk
)

[25-Feb-2022 11:09:33 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Products
)

[25-Feb-2022 11:09:35 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Faq
)

[25-Feb-2022 11:09:37 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Vendors
)

[25-Feb-2022 11:09:39 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => PriceBooks
)

[25-Feb-2022 11:09:42 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Quotes
)

[25-Feb-2022 11:09:44 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => PurchaseOrder
)

[25-Feb-2022 11:09:46 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => SalesOrder
)

[25-Feb-2022 11:09:49 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Invoice
)

[25-Feb-2022 11:09:51 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Campaigns
)

[25-Feb-2022 11:09:53 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => PBXManager
)

[25-Feb-2022 11:09:56 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Services
)

[25-Feb-2022 11:09:59 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => ServiceContracts
)

[25-Feb-2022 11:10:03 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => SMSNotifier
)

[25-Feb-2022 11:10:05 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Assets
)

[25-Feb-2022 11:10:07 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => ModComments
)

[25-Feb-2022 11:10:09 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => ProjectMilestone
)

[25-Feb-2022 11:10:11 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => ProjectTask
)

[25-Feb-2022 11:10:13 America/Mazatlan] Query por ejecutar: SELECT label, crmid, setype, createdtime FROM vtiger_crmentity WHERE label LIKE ? AND vtiger_crmentity.deleted = 0 AND setype = ? con params Array
(
    [0] => %prueba%
    [1] => Project
)

Copyright 2023 Vtiger. All rights reserved.