Database Administration Development Tutorials, Guides & Insights

Indexes are data structures that improve the speed of data retrieval operations on a database table. They are similar to the index in a book, which allows you to quickly find specific topics without scanning every page. In MySQL, indexes can be applied to columns to speed up queries involving those columns.

Indexes can be created when a table is first created or added later using the CREATE INDEX statement.

mysql -u your_username -p your_database_name < backup.sql

• backup.sql: The SQL file containing the exported data.

• Basic understanding of MySQL and SQL operations.
• Access to a MySQL server.
• Familiarity with command-line tools and basic server administration.

The MySQL Performance Schema is a powerful tool for monitoring database performance. It provides a wealth of information about the execution of SQL statements, memory usage, and other performance-related data.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

You can set the isolation level for the current session or globally to affect all new connections.

SELECT table_name AS "Table",
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC
LIMIT 1;

• table_name: The name of each table in the specified database.
• ORDER BY (data_length + index_length) DESC: Orders the tables by size in descending order, so the largest appears first.
• LIMIT 1: Limits the result to only the largest table.