Solved

drop index during the time we clone a MySQL table.

Posted on 2013-11-21
9
375 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 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL Error Code 2 27
AWS EC2 & RDS Instance 5 64
INDEX does not make a difference, why? 10 65
restriction of entering a a page 5 34
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…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…

733 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