Solved

drop index during the time we clone a MySQL table.

Posted on 2013-11-21
9
376 Views
Last Modified: 2013-12-03
Dear all,

Any way to drop the index during the time we clone a table by :


CREATE TABLE IF NOT EXISTS Table B LIKE Table A

Open in new window


which B will copy everything from A when this command execute and not need extra AFTER script manual method?
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 49

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39668237
This isn't possible. This is what the LIKE option does according to documentation:
Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:
You will have to follow-up with subsequent commends (as already provided) to remove any auto-increments and primary key.

forgot the url:
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39668292
so I have to drop manually anyway ?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39668321
manually? that is up to you.

what you cannot do is expect there to be some magic parameter to

             create table audit.x like source. x with magic drop unwanted bits

that magic you have to provide, which "might" be scripted - but again that is up to you.
0
Upcoming Webinar: Securing your MySQL/MariaDB data

Join Percona’s Chief Evangelist, Colin Charles as he presents Securing your MySQL®/MariaDB® data on Tuesday, July 11, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 
LVL 43

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 250 total points
ID: 39668599
String the queries together:

CREATE TABLE IF NOT EXISTS TableB LIKE TableA; ALTER TABLE TableB DROP yourColumn;

Open in new window

If it's just the INDEX you want to drop and not the Column:

DROP INDEX `PRIMARY` ON TableB;

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 39670878
ChrisStanyon,

This means this logic is not automated, I can't use dynamic SQL for it, build a lot recently and see if anything we can check from schema level and, if index found, constraint is found, drop it.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39670883
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39673870
If this is the table structure:

CREATE TABLE `YYYY` (
  `SCHED_NAME` varchar(120) NOT NULL,
  `TRIGGER_NAME` varchar(200) NOT NULL,
  `TRIGGER_GROUP` varchar(200) NOT NULL,
  `CRON_EXPRESSION` varchar(120) NOT NULL,
  `TIME_ZONE_ID` varchar(80) DEFAULT NULL,
  PRIMARY KEY (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  KEY `SCHED_NAME` (`SCHED_NAME`,`TRIGGER_NAME`,`TRIGGER_GROUP`),
  CONSTRAINT `QRTZ_CRON_TRIGGERS_ibfk_1` FOREIGN KEY (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `QRTZ_TRIGGERS` (`SCHED_NAME`, `TRIGGER_NAME`, `TRIGGER_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Open in new window


Then after I clone the table using "like", the just issue:

DROP INDEX `PRIMARY` ON YYYY; 

Open in new window


I read this :
stackoverflow.com/questions/3798524/mysql-dropping-all-indexes-from-table?s=ca15d66a-5c5f-459f-80f5-58430b24626e#new-answer

can the SHOW INDEX FROM <table thing> plugable to the cursor and let we loop that one by one ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39676663
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39677186
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question