Search both Item Name and Item SKU to find item in Invoice

Need to search both Item Name and Item SKU when entering items into an Invocie. Also, need an option to display the SKU number. This is pretty important in most inventory based operations.

For speed:

1. Index the name


2. Index the SKU


3. Use a UNION to avoid a full table scan

 

DDL:

ALTER TABLE `rqu_items`
ADD  INDEX `rqu_items_name` (`company_id`, `name`),
ADD  UNIQUE INDEX `rqu_items_sku_unique` (`company_id`, `sku`);

Example DML:

SELECT
  rqu_items.*
FROM rqu_items
WHERE rqu_items.company_id = {companyid}
AND rqu_items.name LIKE '{BeginsWith%}'
AND rqu_items.deleted_at IS NULL
UNION
SELECT
  rqu_items.*
FROM rqu_items
WHERE rqu_items.company_id = {companyid}
AND rqu_items.sku LIKE '{BeginsWith%}'
AND rqu_items.deleted_at IS NULL

 

<p>Need to search both Item Name and Item SKU when entering items into an Invocie. Also, need an option to display the SKU number. This is pretty important in most inventory based operations.</p><br /> <p>For speed:</p><br /> <ol><li>Index the name</li><br /> <li>Index the SKU</li><br /> <li>Use a UNION to avoid a full table scan</li><br /> </ol><br /> <br /> <p> </p><br /> <p>DDL:<br><br>ALTER TABLE `rqu_items`<br>ADD  INDEX `rqu_items_name` (`company_id`, `name`),<br>ADD  UNIQUE INDEX `rqu_items_sku_unique` (`company_id`, `sku`);<br><br>Example DML:<br><br>SELECT<br>  rqu_items.*<br>FROM rqu_items<br>WHERE rqu_items.company_id = {companyid}<br>AND rqu_items.name LIKE '{BeginsWith%}'<br>AND rqu_items.deleted_at IS NULL<br>UNION<br>SELECT<br>  rqu_items.*<br>FROM rqu_items<br>WHERE rqu_items.company_id = {companyid}<br>AND rqu_items.sku LIKE '{BeginsWith%}'<br>AND rqu_items.deleted_at IS NULL</p><br /> <p> </p>

Hello Van,

Your contribution via GitHub is appreciated https://github.com/akaunting/akaunting

Regards

<p>Hello Van,</p><br /> <p>Your contribution via GitHub is appreciated https://github.com/akaunting/akaunting</p><br /> <p>Regards</p>