Keeping an eye on cardinality stats of your indexes, after creation, alters or large data changes (which OPTIMIZE TABLE will cover) is a good idea when you see inconsistent results. So perhaps what led to this fluctuation in your data, perhaps its even a new logical volume. I have not seen it in years, but i also no longer work with large and VERY active datasets since I used to see it in our query results. On Tue, Mar 5, 2024 at 2:18 PM Jan Křístek via discuss < discuss@lists.mariadb.org> wrote:
I guess I experienced this behaviour a few times when the index was broken or out of sync. I guess that some index checks returned, that it's not in a healthy state. That was a few years back so I don't recall better details...
On Tue, 5 Mar 2024 at 11:03, Jef Van Loon via discuss < discuss@lists.mariadb.org> wrote:
Hi Sergei,
I am currently not able to provide further details.
System 1: Unfortunately I have not saved the queries and results from the analysis. I have not seen the problem since the OPTIMIZE table. I will investigate during the month (we will have to wait for users entering data in this table) and get back to this discussion if I encounter the issue again. What still makes me uncomfortable is that the problem went away after dropping/recreating an index and reappeared later. Also importing a dump of the database into a dev environment did not reveal the same problem.
Do you have any insight if there are cases where index malfunctioning may cause inconsistent results (depending on whether the index is being used) and a drop/recreate does not fully serve the problem. I would be surprised if drop/recreate leads to different results than optimize table. As far as I understand, optimize does also rebuild indexes in addition to rebuilding the entire table file on disk (which would be more or less the same as importing a dump into another system).
System 2: The problem appeared to be the same as on system 1, but further analysis revealed that this was completely unrelated even though the symptoms initially lead me to the conclusion that this would be the same issue.
Kind regards, Jef _______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org
_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org