MySQL: Find the source and destination of all foreign key constraints

We can easily find all foreign keys of a MySQL Database using this query.

SELECT referenced_table_name AS ParentTable,
       table_name            AS ChildTable,
       constraint_name       AS ConstraintName
FROM   information_schema.key_column_usage
WHERE  referenced_table_name IS NOT NULL
       AND table_schema = '<your_db_name>' -- optional
ORDER  BY referenced_table_name;

MySQL: Find missing indexes

Foreign-key columns that lack an index are one of the most common and silent performance killers in a MySQL database. Joins slow down, nested loops explode in cost, and the query plan gives you no obvious warning. Run the query below against any schema and you will get a full list of every *_id column that is sitting without an index.

This is especially useful after a schema review, an ORM migration, or when onboarding to an unfamiliar codebase where index discipline may have been uneven.

---

The problem

ORMs like Entity Framework Core create foreign-key columns reliably but do not always create the corresponding index. MySQL does not enforce indexes on foreign-key columns the way it enforces them on primary keys. The result is that order_id, customer_id, and product_id columns accumulate in your schema with no index, and every join or filtered lookup against them performs a full or partial table scan.

---

The fix

Run this against the schema you want to audit. It finds every column whose name ends in _id and reports whether that column is covered by an index.

SELECT   t.table_schema,
         t.table_name,
         c.column_name,
         IFNULL(kcu.constraint_name, 'Not indexed') AS Indexed
FROM     information_schema.tables AS t
         INNER JOIN information_schema.columns AS c
                 ON c.table_schema = t.table_schema
                AND c.table_name   = t.table_name
                AND c.column_name LIKE '%_id'
         LEFT JOIN information_schema.key_column_usage AS kcu
                ON kcu.table_schema    = t.table_schema
               AND kcu.table_name      = t.table_name
               AND kcu.column_name     = c.column_name
               AND kcu.ordinal_position = 1
WHERE    kcu.table_schema IS NULL
         AND t.table_schema NOT IN (
             'INFORMATION_SCHEMA',
             'PERFORMANCE_SCHEMA',
             'MySQL'
         );

The ordinal_position = 1 filter is important. An index only helps a join or a predicate on a column if that column is the leftmost key in the index definition. A column that appears second or third in a composite index does not count as indexed for this purpose and will still show up in the results.

Note: The original query had a bug in the LEFT JOIN: kcu.column_name = t.column_name was joining against the table row rather than the column row. The corrected version above uses kcu.column_name = c.column_name, which is what was intended.

---

When to use this

This query is a diagnostic, not a substitute for a proper index review. Use it in the following situations:

• After running migrations on a production or staging database to catch any missing indexes the migration did not generate.
• During a performance investigation where slow join queries are surfacing in the slow query log or EXPLAIN output.
• As part of a periodic database health check, especially on databases that grow through ORM-driven schema changes rather than hand-written DDL.
• When taking over an inherited codebase and needing a quick baseline of the index landscape.

Once you have the list, evaluate each result in context. Not every *_id column warrants its own index; columns on small lookup tables, columns that are never used in a join or WHERE clause, and columns already covered by an existing composite index may not need one. The query tells you what is missing; your query plans and workload tell you what actually matters.

Found this useful? Drop a comment below if you have a variation or ran into an edge case I did not cover.

SQL Server: Move Database Files to another drive

At some point you will need to move a SQL Server database to a different drive, whether that is to free up space on an overloaded volume, migrate to faster storage, or reorganise after a server rebuild. SQL Server lets you do this cleanly with ALTER DATABASE, without detaching or restoring from backup.

By the end of this post you will have moved both the data file (.mdf) and the log file (.ldf) to a new location and verified the result, all without touching SQL Server Configuration Manager.

---

1 Prerequisites

Before starting, make sure you have:

• A SQL Server instance with sysadmin or dbcreator rights.
• The destination folder already created on disk with the SQL Server service account granted full control.
• The physical .mdf and .ldf files manually copied to the new location before bringing the database back online.

Important: ALTER DATABASE ... MODIFY FILE updates the path recorded in SQL Server's metadata. It does not move the files on disk. You must copy the files yourself while the database is offline, then set it back online.

---

2 Take the database offline

Taking the database offline flushes all pending writes, closes open connections, and releases the file locks SQL Server holds on the .mdf and .ldf files. You cannot copy a locked file, so this step must come first.

ALTER DATABASE MyNewDatabase
SET OFFLINE;
GO

Once this completes without error, you can safely copy the files at the OS level to E:\New_location\ or wherever your target path is.

---

3 Update the file paths in metadata

With the files copied, tell SQL Server where to find them. Run one MODIFY FILE statement per file, referencing the logical name of each file. You can find the logical names in sys.master_files if you are unsure.

ALTER DATABASE MyNewDatabase MODIFY FILE (
     NAME     = MyNewDatabase_Data
    ,FILENAME = 'E:\New_location\MyNewDatabase_Data.mdf'
    );
GO

ALTER DATABASE MyNewDatabase MODIFY FILE (
     NAME     = MyNewDatabase_Log
    ,FILENAME = 'E:\New_location\MyNewDatabase_Log.ldf'
    );
GO

SQL Server will confirm each change with a message along the lines of File 'MyNewDatabase_Data' modified in the system catalog. The new path will be used the next time the database is started. That message is expected and correct.

---

4 Bring the database back online

With the metadata updated and the files in place at the new path, set the database back online. SQL Server will open the files from the new location.

ALTER DATABASE MyNewDatabase
SET ONLINE;
GO

Tip: If SET ONLINE fails with a file-not-found error, the most common cause is that the files were not copied before this step, or the SQL Server service account does not have read and write permission on the destination folder.

---

5 Verify the new location

Confirm that SQL Server is using the new paths and that the database is online before considering the job done.

-- Check whether the database is moved to your desired location
SELECT name
      ,physical_name AS NewLocation
      ,state_desc    AS OnlineStatus
FROM sys.master_files
WHERE database_id = DB_ID(N'MyNewDatabase')
GO

You should see both rows pointing to E:\New_location\ with an OnlineStatus of ONLINE. If either file still shows the old path, the MODIFY FILE statement for that file did not execute successfully.

---

Wrapping up

Moving SQL Server database files is a four-step operation: take the database offline, copy the files at the OS level, update the metadata with ALTER DATABASE MODIFY FILE, then bring the database back online. The verification query against sys.master_files gives you a definitive confirmation that the move completed as expected.

If you need to do this across several databases at once, the same pattern can be wrapped in a cursor or applied through a scripted approach using sys.master_files to generate the statements dynamically.

Got a question or ran into a problem? Drop a comment below and I will reply.