Optimizing MariaDB Queries: Adding Indexes with ALTER TABLE

Indexes are special data structures that act like an organized filing system for your table data. They significantly improve the speed of fetching specific rows based on certain column values. When you query a table using a WHERE clause that references indexed columns, MariaDB can quickly locate the relevant rows by following the index instead of scanning the entire table.

Adding Indexes with ALTER TABLE

The ALTER TABLE statement in MariaDB allows you to modify the structure of existing tables. One of its functionalities is adding indexes to existing columns. Here's the basic syntax:

ALTER TABLE table_name
ADD INDEX index_name (column_name1, column_name2, ...);

• table_name: The name of the table you want to add the index to.
• index_name: A user-defined name for the index (optional, but recommended for clarity).
• column_name1, column_name2, ...: A comma-separated list of columns to be included in the index. You can index multiple columns for more complex queries.

Example

Suppose you have a table named customers with columns id (primary key), name, and city. You frequently query for customers based on their city. Here's how to add an index on the city column:

ALTER TABLE customers
ADD INDEX city_index (city);

Considerations

• Choosing Columns: Index columns that are frequently used in WHERE clause conditions, especially for equality comparisons (=, !=) or range queries (<, >, BETWEEN).
• Multiple Columns: Consider compound indexes (including multiple columns) for queries that involve filtering on those columns together.
• Trade-offs: Indexing improves query speed but adds some overhead during data insertion and updates (as the index needs to be maintained). Choose indexes judiciously based on your query patterns.

Additional Tips

• Use SHOW INDEX to view existing indexes on a table.
• Analyze your queries to identify bottlenecks and see if indexes would be beneficial.
• Consider using tools like EXPLAIN to understand how MariaDB is using indexes for your queries.

This example adds an index named name_index to the name column in the customers table:

ALTER TABLE customers
ADD INDEX name_index (name);

Example 2: Compound Index

This example creates an index named city_state_index on both the city and state columns in the customers table (useful for queries filtering on both city and state):

ALTER TABLE customers
ADD INDEX city_state_index (city, state);

Example 3: Unique Index

This example adds a unique index named email_unique to the email column in the users table, ensuring no duplicate email addresses exist:

ALTER TABLE users
ADD UNIQUE INDEX email_unique (email);

Remember:

• Replace customers, users, name, city, state, and email with your actual table and column names.
• Choose meaningful index names for better readability.
• Consider the trade-offs between indexing and performance impact when deciding which columns to index.

1. Table Recreation with Indexes:

   • If your table is relatively small or you're comfortable with some downtime, you can recreate the entire table with the desired indexes included in the CREATE TABLE statement. This ensures proper indexing from the start but can be disruptive for larger tables or ongoing operations.

   Here's an example:

   -- 1. Create a temporary table with the same structure as the original
   CREATE TABLE temp_customers LIKE customers;
   
   -- 2. Insert data from the original table into the temporary table
   INSERT INTO temp_customers SELECT * FROM customers;
   
   -- 3. Drop the original table
   DROP TABLE customers;
   
   -- 4. Create a new table with the desired indexes
   CREATE TABLE customers (
       id INT PRIMARY KEY,
       name VARCHAR(255) NOT NULL,
       city VARCHAR(100) NOT NULL,
       INDEX city_index (city) -- Add the index here
   );
   
   -- 5. Insert data from the temporary table into the new table
   INSERT INTO customers SELECT * FROM temp_customers;
   
   -- 6. Drop the temporary table
   DROP TABLE temp_customers;
   

2. Partitioning (Advanced):

Choosing the Right Approach:

• For most cases, using ALTER TABLE with ADD INDEX is the recommended approach for adding indexes to existing tables. It's a straightforward and efficient method.
• Consider table recreation only for small tables or situations where downtime is acceptable.
• Partitioning is a complex option best suited for experienced users managing extraordinarily large tables.