Slow performance between two similar servers neither had an index for query

MySQL & MariaDB Other MySQL® Questions
1

General question, I’m inherited MySQL and learning about it. My background is mostly with SQL Server and a little Oracle.

We have a query in Production and Pre-Production. (same dataset size)

  • Query runs several seconds (5-7) in Prod
  • Query runs in a few hundred ms in pre-prod
  • Both are on SSD.
  • The query is not using an index performs table scan on both.

In MS SQL there are table statistics that help with queries even if there is not an index when stats are updated will improve performance.
Running “ANALYZE TABLE” had no affect.

I did end up suggesting adding the appropriate index which solved the problem.

HOWEVER, why would it have been an issue in the first place, the query without an index ran fine for past year…no issues. No config changes in past year. (of course without the index it probably didn’t run optimally- but didn’t run as slow as several seconds)

I suppose the dataset could have grown significantly, but then when do table statistics get updated. Also are statistics only captured
for index columns how about non indexed columns?

Thanks for any insight.