Inefficient query in Database Driving Provider?

Ignition
1

We had a mysterious mysql database issue the other day and while debugging some of that I noticed some odd queries running from the ignition instance. I suspect the index got corrupted even though repairing does not fix it but that is another issue I suspect.

The query looks something like:

SELECT `id`, `intvalue`, `floatvalue`, `stringvalue`, `datevalue`, `dataintegrity` FROM sqlt_core WHERE `valuechange`>? and `configchange`<? AND `drivername`<>?

The first issue I noticed was this clause drivername<>?. The second issue is that the ? parameter was a lower case version of the Driver Name which happens to be entered in all upper case in the configuration.

So first thing I notice is this query will end up doing a full table scan as most indexes don’t really apply according the explain plan. It only has something like 120000 rows in sqlt_core so not that big of an issue unless is always used. This query was running for 1+ days when I found it according to mysql which is obviously a problem but the execution time is most likely due to mysql corruption.

Ultimate question is this query doing the right thing? It may be that it works correctly due to the incorrect case which just happens make things work and if we lower cased the driver name it would stop working.

Misc Info:
Ignition 7.4.3 32-bit
Maria DB 5.5.27

2

Hi,

That is the normal query used for database driving providers to query the value changes provided by other drivers.

Generally it isn’t an issue because the valuechange and configchange columns are indexed, so the query plan should use those, and then pare down the results on the drivername. However, maybe in this case something different is happening? Could you run “EXPLAIN” with that query and see what it says? You might need to throw in some realistic parameter values to do that.

As for the driver name case, by default, mysql string comparisons are case insensitive. You can find a bit more information here. If you were to index the drivername, perhaps then the case would come into play more.

Are many of the tags driven externally? If so, you might do better to have separate schemas and tag providers for external tags vs. internal (or driven by ignition). A plain “db provider” doesn’t include the driver name in the query, for what it’s worth.

Regards,