[SOLVED] Mysql slow queries - igniton tables

Ignition
,
42

Hello, thank you so much for that. It helps a lot, I appreciate it.

43

Also, I am still getting one full table scan but in this part:

FROM tablesubgeneral pl JOIN tableGeneral pc USING (d2) JOIN tablePr pr ON pr.col8 = pl.f4
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.detail != "0";

doing what you told me, attached conditons says that is there, like is not taking any index and I have an index for fechaHora_Ar

44

The problem is that you are using expressions and functions with the table values in your where clauses, to conform to the format of your string constants. This is unoptimizable.

Instead, convert your constants of your comparisons into the same data types as the necessary table columns, so that all of the computations happen with the constants, not the columns. Then the MySQL query planner can use those values with indices.

That is, your where clause should end up looking like this:

WHERE pc.fechaHora_Ar BETWEEN (...some expression...) AND (...some expression...) AND pl.detail != "0"
3 Likes
45

Thanks, let me try something different, thank you so much.

46

This website does a pretty good job of walking through some of the basics and pitfalls of indexing. It's a pretty interesting read if you have some time to spare:

3 Likes
47

Thank you so much.