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.
2
Answers
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.
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
I’ll leave you to build the alter statement and insert into the if statement.
given