SQL Statements and MariaDB's Internal Table Statistics

• SQL Statements & Structure
  SQL (Structured Query Language) is the language you use to interact with MariaDB. You wouldn't directly use SQL statements to modify the mysql.table_stats table itself. However, some SQL statements might leverage the information stored there to optimize performance. For instance, the EXPLAIN statement can use table statistics to suggest the most efficient way to execute a query.
• Engine-Independent Table Statistics
  This table holds statistics on various tables within your MariaDB database, regardless of the storage engine used (like InnoDB or MyISAM).
• System Table
  MariaDB stores information about its own workings in special tables. These aren't meant to be directly manipulated by users, but provide insights into the database's operation. mysql.table_stats is one such table.

• You wouldn't write code to modify the catalog directly, but a librarian might use it to find books faster (similar to how SQL statements can leverage the statistics).
• Imagine a library. mysql.table_stats is like a card catalog that keeps track of how many books are on each shelf (table).

---

Using ANALYZE TABLE to update statistics

This isn't a direct query of the table, but it's related because ANALYZE TABLE updates the statistics stored in mysql.table_stats.

ANALYZE TABLE your_database.your_table;

This command gathers statistics about the data distribution in your table, which can help the optimizer choose better execution plans for future queries.

Using EXPLAIN to see how statistics are used

EXPLAIN SELECT * FROM your_database.your_table WHERE id > 10;

This EXPLAIN statement shows how the optimizer will approach the query. It might mention using index statistics (which are derived from mysql.table_stats) to determine if an index can be used efficiently.

Checking table statistics with information_schema

While not directly querying mysql.table_stats, you can use views from the information_schema database to get some statistics:

SELECT TABLE_NAME, TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database';

This retrieves the number of rows (an estimate based on mysql.table_stats) for each table in your chosen database.

---

1. Using INFORMATION_SCHEMA Views
   

The information_schema database provides views with information about your database objects. While not as detailed as mysql.table_stats, some views offer insights into table statistics. Here are some examples:

• INNODB_TABLES: (For InnoDB tables) Provides details like data and index sizes, page counts, etc.

• TABLES: Retrieves basic information like table names, estimated row counts, and engine types.

1. SHOW TABLE STATUS statement
   

This statement retrieves various details about your tables, including some statistics like:

• Data and index lengths
• Average row length
• Row format
• Engine used
• Creation time

1. Performance Monitoring Tools
   

Several tools can provide more comprehensive statistics about your database performance. These tools often analyze information similar to what mysql.table_stats stores and offer visualizations or alerts.

1. userstat Plugin (Optional)
   

MariaDB offers a plugin called userstat that can be enabled for more advanced statistics. It provides statements like SHOW TABLE_STATISTICS which offer detailed information on specific tables. However, enabling plugins requires some configuration and might not be suitable for all situations.

Essentially, the approach depends on the specific type of information you need.

• If you need ongoing performance monitoring, consider dedicated tools.
• For a more detailed snapshot of a table's state, SHOW TABLE STATUS might be sufficient.
• For basic information like row counts or engine types, INFORMATION_SCHEMA views can be helpful.