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.
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...
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