[SOLVED] Mysql slow queries - igniton tables

Esmeralda_Lopez1 · October 23, 2023, 3:54pm

Good morning here!

I have a project working with different windows by a Menu, also with tables and when the aplication is working, is getting slow and usually to open a different window is taking more time that should.

I scanned the queries with a software and told me that one query have a average latency with 16 seconds, but the other ones have 1 to 3 seconds. I think is for the query in some part that the application is slow but not at all because this happens in different times, not just only opening that window that have the long query.

I am trying to optimize the query using other function or method, if you have some ideas, will be great, please.

Before I got the error with [ java heap space] (Error ignition java heap space) and I found was a component consuming the memory and the application was faster but again is slow.

The application has been working with a database for almost a year, so maybe needs maintenance, by the way I will let the query here:

select pc.fechaHora_ped as 'Fecha',pr.no_s as 'No. S', pr.no_c as 'No. C', pc.no_pedido as 'No. Pedido', pc.idNum as 'No. Num',
IF(pl.detalle= 0, 'Great',
IF(pl.detalle= 2,'Error 1',
IF(pl.detalle= 4,'Error 2 ',
IF(pl.detalle= 16,'Error 3',
IF(pl.detalle= 20,'Error 4',
IF(pl.detalle= 30,'Error 5',
IF(pl.detalle= 40,'Error 6',
IF(pl.detalle= 50,'Error 7',
IF(pl.detalle= 60,'Error 8',
IF(pl.detalle= 70,'Error 8',
IF(pl.detalle= 80,'Error 9',pl.detalle)))))))))))
 AS "Detail",
IF(pl.detalle= 0,'succesfull',
IF(pl.detalle= 2,'description 1',
IF(pl.detalle= 4,'description 2',
IF(pl.detalle= 16,'description 3',
IF(pl.detalle= 20,'description 4',
IF(pl.detalle= 30,'description 5',
IF(pl.detalle= 40,'description 6',
IF(pl.detalle= 50,'description 7',
IF(pl.detalle= 60,'description 8',
IF(pl.detalle= 70,'description 9',
IF(pl.detalle= 80,'description 10',pl.detalle)))))))))) )
 AS "Description of detail"
FROM table1 pl JOIN tabla2 pc USING (no_pedido) JOIN tabla3 pr ON pr.cod_prod_cliente = pl.cod_prod
WHERE date(pc.fechaHora_Ar)>= current_date() - INTERVAL 1 DAY  AND Time(pc.fechaHora_Ar) BETWEEN '20:00:00' AND '23:59:59' or date(pc.fechaHora_Ar)>= current_date() AND Time(pc.fechaHora_Ar) BETWEEN "00:00:00" AND '14:00:00' and pl.detalle != "0";

Siebold · October 23, 2023, 4:02pm

Not sure if your tables have indexes on the columns but that can improve performance a lot. If they are not indexed then the OR in you where clause is gonna cause lots of performance issues. You could try using a union with two different queries to try to help improve. Maybe something like this?

SELECT 
FROM 
WHERE date(pc.fechaHora_Ar) >= current_date() - INTERVAL 1 DAY
AND TIME(pc.fechaHora_Ar) BETWEEN '20:00:00' AND '23:59:59'
AND pl.detalle != '0'
UNION ALL
SELECT 
FROM 
WHERE date(pc.fechaHora_Ar) >= current_date()
AND TIME(pc.fechaHora_Ar) BETWEEN '00:00:00' AND '14:00:00'
AND pl.detalle != '0';

Esmeralda_Lopez1 · October 23, 2023, 4:07pm

Yes, they have some indexes, but to add data into a column from other table

I am still looking for best practices and I will check what you told me. Thanks

Esmeralda_Lopez1 · October 24, 2023, 12:00am

I tried this way and replacing IF for case but did not worked

carlosbqz · October 24, 2023, 12:14am

I was near to suggest that replacing all the IFs with a single CASE statement would improve the perfomance but you've already tried that

Maybe doing subqueries in your joins to limit the data that you are consulting

jpark · October 24, 2023, 12:26am

Did you change the default innodb buffer pool size?

Tim_Carlton · October 24, 2023, 1:52pm

It isn't likely to improve performance but I would suggest adding a minor entity table to make the query easier to read and maintain.
Something like

CREATE TABLE Detalls
    DetallCode INT,
    Name VARCHAR(20),
    Description VARCHAR(200)

If there isn't an index on the join columns then that would help, as would an index on fechaHora_ar.

Is table1.detalle a number or a string? In the IF comparisons you are comparing it to a number (0, 10, 20, 30, ...) but in the WHERE clause you compare it to a string ("0").
You should do the comparison against the native datatype - either always a number or a string. If the database is storing numbers then this query will force the engine to convert each row in the database to a string for the comparison.
Once the WHERE is comparing against the native data type then you can consider if adding an index to detalle would help. If there are very few that = 0 then it likely would not help but if the data is distributed more evenly then it may.
What database are you using? Some have tools that will show an execution plan and help determine where to add indexes.

Esmeralda_Lopez1 · October 24, 2023, 2:15pm

I will try with subqueries, thanks and yes, with a case is still doing the same.

Esmeralda_Lopez1 · October 24, 2023, 2:16pm

I also remove one select because after analyze is not necesary but still slow,so, the querie will be :


select pc.fechaHora_ped as 'Fecha',pr.no_s as 'No. S', pr.no_c as 'No. C', pc.no_pedido as 'No. Pedido', pc.idNum as 'No. Num',
IF(pl.detalle= 0, 'Great',
IF(pl.detalle= 2,'Error 1',
IF(pl.detalle= 4,'Error 2 ',
IF(pl.detalle= 16,'Error 3',
IF(pl.detalle= 20,'Error 4',
IF(pl.detalle= 30,'Error 5',
IF(pl.detalle= 40,'Error 6',
IF(pl.detalle= 50,'Error 7',
IF(pl.detalle= 60,'Error 8',
IF(pl.detalle= 70,'Error 8',
IF(pl.detalle= 80,'Error 9',pl.detalle)))))))))))
 AS "Detail"
FROM table1 pl JOIN tabla2 pc 
USING (no_pedido) 
JOIN tabla3 pr ON pr.cod_prod_cliente = pl.cod_prod
WHERE date(pc.fechaHora_Ar)>= current_date() - INTERVAL 1 DAY  AND Time(pc.fechaHora_Ar) BETWEEN '20:00:00' AND '23:59:59' or 
date(pc.fechaHora_Ar)>= current_date() AND Time(pc.fechaHora_Ar) BETWEEN "00:00:00" AND '14:00:00' and pl.detalle != "0";

Esmeralda_Lopez1 · October 24, 2023, 2:17pm

I don´t think so, let me check that

Esmeralda_Lopez1 · October 24, 2023, 2:19pm

Thanks, let me try this too adding the index to the columns that are use it for JOIN

I am using MYSQL WORKBENCH 8.0

table1.detalle is a string that shows the value with numbers but I need to show it later in a column table as 'Erro2' for example. Then that is why I use the IF condition, to replace just visually numbers for letters

bkarabinchak.psi · October 24, 2023, 2:25pm

IF(pl.detalle= 0, 'Great', IF(pl.detalle= 2,'Error 1', IF(pl.detalle= 4,'Error 2 ', IF(pl.detalle= 16,'Error 3', IF(pl.detalle= 20,'Error 4', IF(pl.detalle= 30,'Error 5', IF(pl.detalle= 40,'Error 6', IF(pl.detalle= 50,'Error 7', IF(pl.detalle= 60,'Error 8', IF(pl.detalle= 70,'Error 8', IF(pl.detalle= 80,'Error 9',pl.detalle)))))))))))

I would make a lookup table for this part. Have a table with two columns, something like detalleId and errorCode, and JOIN on that. Much simpler than 10 nested IF statements, and if you ever need to add a new error code, you just add a new row and it will Just Work.

Indexes on the columns you JOIN with is essential otherwise you're doing full table lookups. If you can run the query in MySQL Work Bench then click on the Query Menu at top -> Explain Current Statement, it should show you the execution plan. You want to see where there are any full table look ups that you can eliminate. Won't always be possible but if there's any you see with tables you are JOINing on - that's where you want an index.

Tim_Carlton · October 24, 2023, 2:32pm

If it is a string then you should compare it to a string, e.g.

IF(pl.detalle= "0", 'Great'
instead of

IF(pl.detalle= 0, 'Great'

But mostly you should replace that with a lookup table

select pc.fechaHora_ped as 'Fecha',pr.no_s as 'No. S', pr.no_c as 'No. C', pc.no_pedido as 'No. Pedido', pc.idNum as 'No. Num', 
d1.Name, d1.description
FROM table1 p1 JOIN detalls d1 ON p1.detalle = d1.detalle JOIN ...

Esmeralda_Lopez1 · October 24, 2023, 4:54pm

I am using Mysql Workbench 8.0

bkarabinchak.psi · October 24, 2023, 4:56pm

It should have the Query menu still and Explain Current Statement option. If you have trouble finding it you can also put EXPLAIN in front of your SELECT query as well.

Esmeralda_Lopez1 · October 24, 2023, 5:35pm

I got this:

bkarabinchak.psi · October 24, 2023, 5:38pm

One thing I see is you have a possible_key in your second row, but the actual key it uses is NULL meaning it's not using one despite there being a candidate. What if you drop USING (no_pedido) from your query and then try explaining it - does that key column fill in? I would say it's rare you actually have to tell MySQL what column key to use, especially in a query like you have here.

Esmeralda_Lopez1 · October 24, 2023, 5:43pm

I got this, seems like there aren´t possible keys, after removing using(no_pedido) :

bkarabinchak.psi · October 24, 2023, 5:53pm

Oh my fault I misinterpreted the query. USING(no_pedido) is fine.

Can you post the results of SHOW CREATE TABLE table1 and for each table? So I can see the full schema and extent of the relationships. Perhaps all you really want or need is a foreign key here, but I need to how these tables interrelate.

If the tables are very wide with a lot of columns, just make sure you include the definitions of any columns used in a JOIN (like no_pedido, cod_prod_cliente, cod_prod) and anything used in a WHERE clause at minimum.

Esmeralda_Lopez1 · October 24, 2023, 6:40pm

I think would be better if I recreate an schema with the same relations and tables, just different names