# \[SOLVED\] Mysql slow queries - igniton tables

**URL:** <https://forum.inductiveautomation.com/t/solved-mysql-slow-queries-igniton-tables/80866>\
**Category:** Ignition\
**Tags:** ignition81, vision\
**Created:** [October 23, 2023, 3:54pm UTC](https://forum.inductiveautomation.com/t/solved-mysql-slow-queries-igniton-tables/80866 "2023-10-23T15:54:12Z")\
**Posts on this page:** 6\
**Page:** 3

<div class="post-metadata">

**Author:** ![Esmeralda\_Lopez1](https://avatars.discourse-cdn.com/v4/letter/e/cdc98d/32.png) [@Esmeralda\_Lopez1](https://forum.inductiveautomation.com/u/Esmeralda_Lopez1)\
**Post date:** [October 26, 2023, 8:01pm UTC](https://forum.inductiveautomation.com/t/solved-mysql-slow-queries-igniton-tables/80866/42 "2023-10-26T20:01:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Esmeralda\_Lopez1](https://avatars.discourse-cdn.com/v4/letter/e/cdc98d/32.png) [@Esmeralda\_Lopez1](https://forum.inductiveautomation.com/u/Esmeralda_Lopez1)\
**Post date:** [October 26, 2023, 8:13pm UTC](https://forum.inductiveautomation.com/t/solved-mysql-slow-queries-igniton-tables/80866/43 "2023-10-26T20:13:47Z")

</div>

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

```auto
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

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [October 26, 2023, 8:31pm UTC](https://forum.inductiveautomation.com/t/solved-mysql-slow-queries-igniton-tables/80866/44 "2023-10-26T20:31:45Z")

</div>

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:

```auto
WHERE pc.fechaHora_Ar BETWEEN (...some expression...) AND (...some expression...) AND pl.detail != "0"

```

---

<div class="post-metadata">

**Author:** ![Esmeralda\_Lopez1](https://avatars.discourse-cdn.com/v4/letter/e/cdc98d/32.png) [@Esmeralda\_Lopez1](https://forum.inductiveautomation.com/u/Esmeralda_Lopez1)\
**Post date:** [October 26, 2023, 10:26pm UTC](https://forum.inductiveautomation.com/t/solved-mysql-slow-queries-igniton-tables/80866/45 "2023-10-26T22:26:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Felipe\_CRM](https://avatars.discourse-cdn.com/v4/letter/f/dfb087/32.png) [@Felipe\_CRM](https://forum.inductiveautomation.com/u/Felipe_CRM)\
**Post date:** [October 27, 2023, 2:27pm UTC](https://forum.inductiveautomation.com/t/solved-mysql-slow-queries-igniton-tables/80866/46 "2023-10-27T14:27:02Z")

</div>

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:

> **[SQL Indexing and Tuning e-Book for developers: Use The Index, Luke covers...](https://use-the-index-luke.com/)**
>
> SQL indexing and tuning tutorial for developers. No unnecessary database details—just what developers need to know. Covers all major SQL databases.

---

<div class="post-metadata">

**Author:** ![Esmeralda\_Lopez1](https://avatars.discourse-cdn.com/v4/letter/e/cdc98d/32.png) [@Esmeralda\_Lopez1](https://forum.inductiveautomation.com/u/Esmeralda_Lopez1)\
**Post date:** [October 27, 2023, 2:51pm UTC](https://forum.inductiveautomation.com/t/solved-mysql-slow-queries-igniton-tables/80866/47 "2023-10-27T14:51:58Z")

</div>

Thank you so much.

[Previous page](https://forum.inductiveautomation.com/t/solved-mysql-slow-queries-igniton-tables/80866.md?page=2)