Home / Questions / 96048
Accepted
Bernardo Harreguy
Asked: 2020-08-20 03:41:06 +0800 CST 2020-08-20 03:41:06 +0800 CST

How to perform a faster MYSQL query

  • 772

Hi how are things? I'm trying to perform a database query (MYSQL) and I need to optimize it to be as fast as possible, for now I've dropped from 2 seconds to 1.27, and it's still slow, Here are the tables that are inside the query:

Price table

CREATE TABLE `precios` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `porcentaje` DECIMAL(4,2) NOT NULL DEFAULT '0.00',
    `precio` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `precio2` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `ganancia` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `costo` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `idproducto` INT(11) NOT NULL DEFAULT '0',
    `numerodepreciodelista` INT(10) NOT NULL DEFAULT '0',
    `impuesto` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    `ingresosbrutos` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`id`, `idproducto`)
)

Bar code table

CREATE TABLE `codigobarras` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `idproducto` INT(11) NOT NULL DEFAULT '0',
    `codigobarras` TEXT NOT NULL,
    `idrubro` INT(11) NOT NULL DEFAULT '0',
    `idpromocion` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)

Product categories table

CREATE TABLE `categoriadeproductos` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nombre` TEXT NOT NULL,
    `ganancia` DECIMAL(10,2) NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`id`)
)

Suppliers table

CREATE TABLE `proveedores` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nombre` VARCHAR(50) NOT NULL DEFAULT '0',
    `descripcion` TEXT NULL,
    `domicilio` VARCHAR(50) NULL DEFAULT NULL,
    `localidad` VARCHAR(50) NULL DEFAULT NULL,
    `telefono` VARCHAR(50) NULL DEFAULT NULL,
    `fax` VARCHAR(50) NULL DEFAULT NULL,
    `codigo postal` VARCHAR(50) NULL DEFAULT NULL,
    `contacto` VARCHAR(50) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)

Products table

    CREATE TABLE `productos` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `nombre` TEXT NOT NULL,
    `categoria` INT(11) NOT NULL,
    `proveedor` INT(11) NOT NULL,
    `cantidad` DECIMAL(11,2) NOT NULL DEFAULT '0.00',
    `cantidadbultoabierto` DECIMAL(11,2) NOT NULL DEFAULT '0.00',
    `cantidadbultocerrado` DECIMAL(11,2) NOT NULL DEFAULT '0.00',
    `stockminimo` DECIMAL(10,2) NOT NULL DEFAULT '4.00',
    `fecha` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `codigobarras` VARCHAR(50) NOT NULL DEFAULT '',
    `imagen` BLOB NULL,
    `eliminado` TINYINT(1) NOT NULL DEFAULT '0',
    `especial` TINYINT(4) NOT NULL DEFAULT '0',
    `ingresosbrutos` DECIMAL(3,2) NOT NULL DEFAULT '0.00',
    `tipodeproducto` INT(11) NOT NULL DEFAULT '0',
    `ultimamodificacion` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `id` (`id`),
    INDEX `nombre` (`id`)
)

Now the query I am doing is the following

SELECT productos.id, productos.nombre as Nombre
, categoriadeproductos.nombre AS Categoria
, proveedores.nombre AS Proveedor 
, cantidad as Cantidad
, stockminimo AS 'Stock Mínimo'
, (precios.precio) AS Precio
, (precios.precio2) AS 'Precio %'
, (precios.costo) AS 'P. costo'
, (precios.ganancia) AS Ganancia
, ultimamodificacion AS 'Fecha última modificación'
, (select codigobarras.codigobarras From codigobarras WHERE codigobarras.idproducto = productos.id LIMIT 1) AS 'Código de Barras' 
FROM productos INNER JOIN (precios, proveedores, categoriadeproductos) ON  proveedores.id = productos.proveedor AND categoriadeproductos.id = productos.categoria AND precios.idproducto = productos.id WHERE precios.numerodepreciodelista = 0 AND productos.eliminado = 0;

It takes approximately always between 1.2 and 1.3 seconds. The explain of the query is as follows:

+------+--------------------+----------------------+--------+-------------------+---------+---------+---------------------------------+------+--------------------------------------------------------+
| id   | select_type        | table                | type   | possible_keys     | key     | key_len | ref                             | rows | Extra                                      |
+------+--------------------+----------------------+--------+-------------------+---------+---------+---------------------------------+------+--------------------------------------------------------+
|    1 | PRIMARY            | precios              | ALL    | NULL              | NULL    | NULL    | NULL                            | 2073 | Using where                                      |
|    1 | PRIMARY            | productos            | eq_ref | PRIMARY,id,nombre | PRIMARY | 4       | sistema_tpv2.precios.idproducto |    1 | Using where                                      |
|    1 | PRIMARY            | proveedores          | ALL    | PRIMARY           | NULL    | NULL    | NULL                            |    2 | Using where; Using join buffer (flat, BNL join)        |
|    1 | PRIMARY            | categoriadeproductos | ALL    | PRIMARY           | NULL    | NULL    | NULL                            |    3 | Using where; Using join buffer (incremental, BNL join) |
|    2 | DEPENDENT SUBQUERY | codigobarras         | ALL    | NULL              | NULL    | NULL    | NULL                            | 1926 | Using where                                      |
+------+--------------------+----------------------+--------+-------------------+---------+---------+---------------------------------+------+--------------------------------------------------------+

Explanation of the query

The query makes a list of all the products found in the database. Each product comprises a category and a supplier, which are pointed to the subsequent tables with their ID or Index. This part is quite optimized, but the complication is with the prices, each product has a cost, a price, a fractional price (price2), a profit based on the cost (profit), and in the prices table, there is the PRODUCTID field that refers to the product , and for each product there may be several prices. Each price is identified by LISTPRICENUMBER, by default it is 0 which is the basic one, and in the query I am only interested in showing this. then in the WHERE I put numberoflistprices = 0. And finally for each product there is a barcode (codigobarras) that can be more than one for each product, and for this reason I have to limit it to 1, as it is in the query, each barcode refers to the product for which PRODUCTID field. In the query I used inner join, as you can see, but I couldn't optimize it for more than 1.20 sec. I hope to find a better way! thanks since now

CLARIFICATION: All Tables are secondary to Products. Secondary tables:

Barcode

Codigobarras contains the product barcode. The idproduct field refers to the id of each product. There can be more than one barcode per product, that is, a product can have more than one barcode.

Prices

prices contains the prices of each product. The productid field refers to the product id. The prices must have as a condition numberoflistprice = 0, because the other prices do not interest me in this query.

Categories

Each product has a category, and in the products table the category field refers to the id of the category to which it belongs

providers

Like categories, each product belongs to a provider and each provider has an id, and the provider field refers to this.

In suppliers and categories there are only 3 records for each table in prices and barcodes there are the same number of records as products, maybe more and products have 2800 records

Here the base sql that I have file.sql

mysql

2 Answers

  1. Best Answer

    The original query, on my test machine, takes 1.4 seconds.

    The query can be improved in two aspects:

    Unnecessary nested query

    The first thing I eliminated was the nested query, since it was unnecessary, since a union of tables with INNER JOINhas better performance than managing an additional query for each record obtained.

    SELECT productos.id,
       productos.nombre AS Nombre,
       categoriadeproductos.nombre AS Categoria,
       proveedores.nombre AS Proveedor,
       cantidad AS Cantidad,
       stockminimo AS 'Stock Mínimo',
       (precios.precio) AS Precio,
       (precios.precio2) AS 'Precio %',
       (precios.costo) AS 'P. costo',
       (precios.ganancia) AS Ganancia,
       ultimamodificacion AS 'Fecha última modificación',
       codigobarras.codigobarras AS 'Código de Barras'
FROM productos
INNER JOIN precios
    ON precios.numerodepreciodelista = 0 AND precios.idproducto = productos.id 
INNER JOIN codigobarras
    ON codigobarras.idproducto = productos.id
INNER JOIN (proveedores, categoriadeproductos)
ON proveedores.id = productos.proveedor
    AND categoriadeproductos.id = productos.categoria
WHERE productos.eliminado = 0
GROUP BY productos.id

    Now the execution time is 850 ms. 40% less (almost half).

    But he DESCRIBEis:

    1       SIMPLE  precios NULL    ALL     NULL    NULL    NULL    NULL    2073    10.00   Using where; Using temporary; Using filesort
1       SIMPLE  productos       NULL    eq_ref  PRIMARY,id,nombre       PRIMARY 4       pruebas.precios.idproducto      1       10.00   Using where
1       SIMPLE  proveedores     NULL    ALL     PRIMARY NULL    NULL    NULL    2       50.00   Using where; Using join buffer (Block Nested Loop)
1       SIMPLE  categoriadeproductos    NULL    ALL     PRIMARY NULL    NULL    NULL    3       33.33   Using where; Using join buffer (Block Nested Loop)
1       SIMPLE  codigobarras    NULL    ALL     NULL    NULL    NULL    NULL    1926    10.00   Using where; Using join buffer (Block Nested Loop)

    table indices

    The second problem (the most important) that your query suffers from is that associations between tables are made using fields that are not indexed, so to find them you have to go through all the records of the table over and over again.

    If we add an index to each related field and to eliminado(which has important relevance in this query):

    ALTER TABLE `precios` ADD INDEX( `idproducto`);
ALTER TABLE `codigobarras` ADD INDEX(`idproducto`);
ALTER TABLE `productos` ADD INDEX( `eliminado`);

    We will obtain a reduction in the query time to only 140 ms, 90% less than the initial query (one tenth) and 84% less than the improved one by removing the nested query.

    In this case DESCRIBEhe gives us:

    id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  proveedores     NULL    ALL     PRIMARY NULL    NULL    NULL    2       100.00  Using temporary; Using filesort
1       SIMPLE  precios NULL    ALL     idproducto      NULL    NULL    NULL    2073    10.00   Using where; Using join buffer (Block Nested Loop)
1       SIMPLE  productos       NULL    eq_ref  PRIMARY,id,nombre,eliminado     PRIMARY 4       pruebas.precios.idproducto      1       9.98    Using where
1       SIMPLE  categoriadeproductos    NULL    ALL     PRIMARY NULL    NULL    NULL    3       33.33   Using where; Using join buffer (Block Nested Loop)
1       SIMPLE  codigobarras    NULL    ref     idproducto      idproducto      4       pruebas.precios.idproducto      1       100.00  NULL
    • 6

  2. I recommend you do a little research on data normalization and relationships in MySQL, currently you are having some problems with field names and that you are not relating the tables or creating indexes for fields like codigodebarras.idproducto, or productos.proveedorwhat should be called in that case idproveedorby the relationship that you are consulting Adding indexes to the fields you relate by and creating the relationships correctly should increase speed.

    I enclose the following Querie to have a little more verbose the query and removing the Subquery.

    SELECT 
pr.id, 
pr.nombre as Nombre,
cp.nombre AS Categoria,
pv.nombre AS Proveedor, 
cantidad as Cantidad,
stockminimo AS 'Stock Mínimo',
(p.precio) AS Precio,
(p.precio2) AS 'Precio %',
(p.costo) AS 'P. costo',
(p.ganancia) AS Ganancia,
ultimamodificacion AS 'Fecha última modificación',
cod.codigobarras AS 'Código de Barras'
FROM productos pr 
INNER JOIN precios p ON p.idproducto = pr.id
INNER JOIN proveedores pv ON pv.id = pr.proveedor 
INNER JOIN categoriadeproductos cp ON cp.id = pr.categoria
INNER JOIN codigobarras cod ON cod.idproducto = pr.id -- Probablemente devuelva mas de un resultados si tenes mas de un Codigo de Barra para un solo producto
WHERE 
p.numerodepreciodelista = 0 AND pr.eliminado = 0;

    I hope this helps to clarify your problem a little more. Cheers

    • 3