?
Solved

drop index during the time we clone a MySQL table.

Posted on 2013-11-21
9
Medium Priority
?
378 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 750 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 43

Assisted Solution

by:Chris Stanyon
Chris Stanyon earned 750 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

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