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 < [email protected]> 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 < > [email protected]> 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 -- [email protected] >> To unsubscribe send an email to [email protected] >> > _______________________________________________ > discuss mailing list -- [email protected] > To unsubscribe send an email to [email protected] >
_______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
