Error when creating Index for table Mysql

Question

I'm working on create index for table with mysql.
I've 2 tables:
1. account

2. x_activity (x is the account_id related to "account" table, EX: 1_activity, 2_activity).
So i've created an "Index" for activity table:
Here are my code:

DROP PROCEDURE if exists update_index_for_table;
DELIMITER $$
CREATE PROCEDURE update_index_for_table()
BEGIN

  DECLARE done INT DEFAULT FALSE;
  DECLARE accountid INT;  
  --
  -- GET ALL ACCOUNT ID
  --

  DECLARE accountids CURSOR FOR SELECT account_id FROM account;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  --
  -- LOOP 
  --  

  OPEN accountids; 
  read_loop: LOOP 
    FETCH accountids INTO accountid;
    IF done THEN 
      LEAVE read_loop;
    END IF;   

    --
    -- INDEX FOR ACTIVITY 
    --  

        SET @update_activity_table_1 = CONCAT("
        IF (
            SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
            WHERE `TABLE_SCHEMA` = DATABASE() AND TABLE_NAME='",accountid,"_activity' AND 
            INDEX_NAME='IDX_",accountid,"_ACTIVITY_ACTIVITY_ID'
            ) != 1
        THEN
            ALTER TABLE ",accountid,"_activity 
                ADD KEY `IDX_",accountid,"_ACTIVITY_ACTIVITY_ID` (`activity_id`);
        END IF;
        ");
    PREPARE stmt from @update_activity_table_1; 
    EXECUTE stmt;

    END LOOP; 
  CLOSE accountids;
END$$
DELIMITER ;
CALL update_index_for_table();

But then, for some php/mysql version (i think), its cause an error like this:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF ( SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS WHERE `TABLE_SCHEM' at line 1

I've tested this code and its work fine:

SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS
WHERE `TABLE_SCHEMA` = DATABASE() AND TABLE_NAME='",accountid,"_activity' AND 
INDEX_NAME='IDX_",accountid,"_ACTIVITY_ACTIVITY_ID'

Here are my php/sql version:
phpmyadmin: 4.8.5, php version: 7.2.7, mysql: 5.6.45

Please help, thanks.

Answer 1

There are a couple of constraints on what you are trying to do here 1) you cannot run an if statement outwith a stored program 2)if you pass a query to dynamic sql and the query does not find anything the continue handler will be invoked and the loop will terminate (unexpectedly) early. The approach then is to split the functionality to first check existence by amending the 'find' to insert a value to a user defined variable and at the same time ensure the handler is not hijacked by a not found by including a look up on a table which will definitely contain something (in this case information.schema_tables.

So given

DROP PROCEDURE if exists p;
DELIMITER $$
CREATE PROCEDURE p()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE accountid INT;
  --
  -- GET ALL ACCOUNT ID
  --
  DECLARE accountids CURSOR FOR SELECT account_id FROM account;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  --
  -- LOOP 
  --  
  OPEN accountids; 
  read_loop: LOOP 
    FETCH accountids INTO accountid;
    select accountid;
    IF done = true THEN 
        select accountid, 'leaving';
      LEAVE read_loop;
    END IF;   
    --
    -- INDEX FOR ACTIVITY 
    --  
   SET @test := 0; 
    SET @update_activity_table_1 :=
        (concat('SELECT case when index_name is null then 1 else 0 end into @test FROM 
         information_schema.tables it
         left join INFORMATION_SCHEMA.STATISTICS iss ',
         ' on iss.table_schema = it.table_schema and iss.table_name = it.table_name and ',
         'INDEX_NAME=',char(39),'IDX_',accountid,'_ACTIVITY_ACTIVITY_ID',char(39),
        ' WHERE it.TABLE_SCHEMA = ', char(39),'test',char(39), ' AND ',
        'it.TABLE_NAME=',char(39),accountid,'_activity', char(39),
        ';'
        )
        )
    ;
   select @update_activity_table_1;

    PREPARE stmt from @update_activity_table_1;
   EXECUTE stmt;
    deallocate prepare stmt;

    if @test = 1 then
        select 'Did not find index for ' , accountid, '_extract';
    else
        select 'Found index for ' , accountid, '_extract';
    end if;

    END LOOP; 
   CLOSE accountids;
END $$
DELIMITER ;
call p();

I'll leave you to build the alter statement and insert into the if statement.

given

    use test;
    drop table if exists account,`1_activity`,`2_activity`,`64_activity`;

    create table account (account_id int);
    create table `1_activity`(id int);
    create table `2_activity`(id int);
    create table `64_activity`(id int);

    insert into account values (1),(2),(64);

MariaDB [test]> call p();
+-----------+
| accountid |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @update_activity_table_1                                                                                                                                                                                                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT case when index_name is null then 1 else 0 end into @test FROM
         information_schema.tables it
         left join INFORMATION_SCHEMA.STATISTICS iss  on iss.table_schema = it.table_schema and iss.table_name = it.table_name and INDEX_NAME='IDX_1_ACTIVITY_ACTIVITY_ID' WHERE it.TABLE_SCHEMA = 'test' AND it.TABLE_NAME='1_activity'; |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

+-------------------------+-----------+----------+
| Did not find index for  | accountid | _extract |
+-------------------------+-----------+----------+
| Did not find index for  |         1 | _extract |
+-------------------------+-----------+----------+
1 row in set (0.28 sec)

+-----------+
| accountid |
+-----------+
|         2 |
+-----------+
1 row in set (0.30 sec)

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @update_activity_table_1                                                                                                                                                                                                                                                                                                                                 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT case when index_name is null then 1 else 0 end into @test FROM
         information_schema.tables it
         left join INFORMATION_SCHEMA.STATISTICS iss  on iss.table_schema = it.table_schema and iss.table_name = it.table_name and INDEX_NAME='IDX_2_ACTIVITY_ACTIVITY_ID' WHERE it.TABLE_SCHEMA = 'test' AND it.TABLE_NAME='2_activity'; |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.30 sec)

+-------------------------+-----------+----------+
| Did not find index for  | accountid | _extract |
+-------------------------+-----------+----------+
| Did not find index for  |         2 | _extract |
+-------------------------+-----------+----------+
1 row in set (0.47 sec)

+-----------+
| accountid |
+-----------+
|        64 |
+-----------+
1 row in set (0.49 sec)

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @update_activity_table_1                                                                                                                                                                                                                                                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SELECT case when index_name is null then 1 else 0 end into @test FROM
         information_schema.tables it
         left join INFORMATION_SCHEMA.STATISTICS iss  on iss.table_schema = it.table_schema and iss.table_name = it.table_name and INDEX_NAME='IDX_64_ACTIVITY_ACTIVITY_ID' WHERE it.TABLE_SCHEMA = 'test' AND it.TABLE_NAME='64_activity'; |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.50 sec)

+-------------------------+-----------+----------+
| Did not find index for  | accountid | _extract |
+-------------------------+-----------+----------+
| Did not find index for  |        64 | _extract |
+-------------------------+-----------+----------+
1 row in set (0.66 sec)

+-----------+
| accountid |
+-----------+
|        64 |
+-----------+
1 row in set (0.67 sec)

+-----------+---------+
| accountid | leaving |
+-----------+---------+
|        64 | leaving |
+-----------+---------+
1 row in set (0.67 sec)

Query OK, 0 rows affected (0.69 sec)

Answer 2

Firstly, i believe accound_id and activity_id are both your unique keys, but what am not sure if you are auto incrementing it, check to see if the auto increment is check.