Solved

drop index during the time we clone a MySQL table.

Posted on 2013-11-21
9
371 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
  • 6
  • 2
9 Comments
 
LVL 48

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 48

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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