In reply to a question about how to conditionally drop an index on a table in MySQL. It appears the syntax doesn’t exist. However, maybe it does and I missed it. If I did miss it, I’m sure somebody will let me know. However, I simply have a dropIndexIfExists stored procedure for this type of database maintenance.

Below is my dropIndexIfExists stored procedure:

-- Conditionally drop the procedure.
DROP PROCEDURE IF EXISTS dropIndexIfExists;

-- Change the default semicolon delimiter to write a PSM
-- (Persistent Stored Module) or stored procedure.
DELIMITER $$

-- Create the procedure.
CREATE PROCEDURE dropIndexIfExists
( pv_table_name  VARCHAR(64)
, pv_index_name  VARCHAR(64))
BEGIN

  /* Declare a local variable for the SQL statement. */
  DECLARE stmt VARCHAR(1024);

  /* Set a session variable with two parameter markers. */
  SET @SQL := CONCAT('ALTER TABLE ',pv_table_name,'DROP INDEX ',pv_index_name);

  /* Check if the constraint exists. …

As I review with my students, a stored function works like a standalone program, while a stored procedure runs in the scope of another program unit. For example, you can compare the result of a function as an expression in an IF statement, like:

  IF add_numbers(1,3) > 3 THEN
    ...
  ELSE
    ...
  END IF;

You can’t call procedures inside an IF statement, but you can call the procedure and use a single OUT-mode (pass-by-reference) parameter from the procedure in a subsequent IF statement. You can implement a a wait procedure like that with the following example.

The example first creates two tables, the road_runner and coyote tables:

-- Drop road_runner table if exists.
DROP TABLE IF EXISTS road_runner;
 
-- Create roadrunner table.
CREATE …