mariadb-query-optimization

Start by running EXPLAIN on the slow query and check for type=ALL, NULL keys, or Using filesort in the output. Then run ANALYZE TABLE to refresh statistics, verify index column order matches your predicates, and use ANALYZE to compare estimated versus actual row counts.

Check the type field for ALL (full table scan), key for NULL (no index used), rows for high scan estimates, and Extra for Using filesort or Using temporary. Using index in Extra indicates a covering index, which is the desired outcome.

MariaDB does not support MySQL's functional key part syntax with doubled parentheses. Instead, create a VIRTUAL generated column with the expression and add an index on that column, then query the generated column directly.

OFFSET scans and discards all skipped rows on every page load, so LIMIT 10 OFFSET 50000 reads 50,010 rows. Cursor-based pagination using WHERE id < last_id seeks directly to the position via the index and stays fast at any depth.

No, Performance Schema is disabled by default and cannot be enabled at runtime. You must set performance_schema=ON in my.cnf and restart the server, then enable the consumers and instruments you need.

Avoid indexing low-cardinality columns like booleans or two-value status fields, since the optimizer prefers a table scan anyway. Also skip indexes on very small tables and rarely queried write-heavy columns, since every index slows INSERT, UPDATE, and DELETE.