Nasrul Hazim Bin Mohamad

MySQL: Bulk Remove Redundant Index

Oct
31
  1. Find the [TARGET_DATABSE] and replace it with the target database
  2. Execute the query
  3. Copy the result and execute each of the SQL statement to remove the index
SELECT
CONCAT(GROUP_CONCAT('ALTER TABLE `',tab_left.TABLE_NAME,'` DROP INDEX ',
(
CASE 
WHEN tab_left.COLUMNS = tab_right.COLUMNS AND (tab_left.IS_UNIQUE = tab_right.IS_UNIQUE) 
THEN GREATEST(tab_left.INDEX_NAME, tab_right.INDEX_NAME) 
ELSE tab_left.INDEX_NAME 
END 
) SEPARATOR ';n')) AS q 
FROM 
(
SELECT 
TABLE_NAME, 
CONCAT('`', INDEX_NAME, '`') AS INDEX_NAME, 
INDEX_TYPE, IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE, 
GROUP_CONCAT(
CONCAT(
'`', COLUMN_NAME, '`') ORDER BY 
IF( INDEX_TYPE = 'BTREE', SEQ_IN_INDEX, 0), 
COLUMN_NAME) COLUMNS FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = '[TARGET_DATABASE]' AND TABLE_NAME = TABLE_NAME GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE) AS tab_left 
INNER JOIN (SELECT TABLE_NAME, CONCAT('`', INDEX_NAME, '`') AS INDEX_NAME, INDEX_TYPE, IF(NON_UNIQUE, 'NO', 'YES') IS_UNIQUE, 
GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') ORDER BY IF(INDEX_TYPE = 'BTREE', SEQ_IN_INDEX, 0), COLUMN_NAME) COLUMNS 
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = '[TARGET_DATABASE]' AND TABLE_NAME = TABLE_NAME 
GROUP BY TABLE_NAME, INDEX_NAME, INDEX_TYPE, NON_UNIQUE) AS tab_right 
ON tab_left.TABLE_NAME = tab_right.TABLE_NAME AND tab_left.INDEX_NAME != tab_right.INDEX_NAME 
AND tab_left.INDEX_TYPE = tab_right.INDEX_TYPE AND CASE WHEN tab_left.COLUMNS = tab_right.COLUMNS 
AND (tab_left.IS_UNIQUE = 'NO' OR tab_left.IS_UNIQUE = tab_right.IS_UNIQUE) 
THEN TRUE WHEN tab_left.INDEX_TYPE = 'BTREE' AND INSTR(tab_right.COLUMNS, tab_left.COLUMNS) = 1 AND tab_left.IS_UNIQUE = 'NO' 
THEN TRUE ELSE FALSE END GROUP BY tab_left.TABLE_NAME; 

MySQL: Bulk PK Rename

Oct
29
SELECT CONCAT(
		GROUP_CONCAT('ALTER TABLE `',a.TABLE_NAME,'`',
		' CHANGE `',a.TABLE_NAME,'`.`',a.COLUMN_NAME,'` ',
		'`',a.TABLE_NAME,'`.`id` ',a.COLUMN_TYPE
	SEPARATOR ';n')
) AS q
FROM `information_schema`.`COLUMNS` a
WHERE a.`TABLE_SCHEMA` = 'target_database' AND a.`COLUMN_KEY` = 'PRI';

Copy & Paste the result in your MySQL editor and execute the SQL.

References
  1. http://stackoverflow.com/questions/7691816/group-concat-comma-separator-mysql
  2. http://stackoverflow.com/questions/893874/mysql-determine-tables-primary-key-dynamically/893922#893922
  3. http://mysql-0v34c10ck.blogspot.com/2011/05/better-way-to-get-primary-key-columns.html

How to set prefix to multiple tables at one time

Oct
23

Set Max Length for Group Concatenation

SET group_concat_max_len = 6144;

Generate the SQL

SELECT 
    CONCAT('RENAME TABLE ', GROUP_CONCAT('`', TABLE_SCHEMA, '`.`', TABLE_NAME, '` TO `', TABLE_SCHEMA, '`.`rgbis_', TABLE_NAME, '`')) AS q
FROM 
    `information_schema`.`Tables` WHERE TABLE_SCHEMA='target_database';

Once above SQL generated, copy & paste in your SQL editor, and execute it.