?
Solved

Activate index/ Deactivate Index in MySQL

Posted on 2007-07-30
9
Medium Priority
?
573 Views
Last Modified: 2013-11-07
Hi..
I wonder how can I deactivate all index in a database and then activate then again
0
Comment
Question by:AWestEng
  • 5
  • 4
9 Comments
 
LVL 17

Accepted Solution

by:
Aleksandar Bradarić earned 2000 total points
ID: 19592630
Use `ALTER TABLE` for each table:
---
ALTER TABLE yourTable DISABLE KEYS;
ALTER TABLE yourTable ENABLE KEYS;
---
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19592654
but I dont want the keys just the index

0
 
LVL 1

Author Comment

by:AWestEng
ID: 19592660
I tested USE INDEX but I got a error
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19592793
> but I dont want the keys just the index

KEYS = INDEXES. What do you refer to as INDEX?
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19592904
oi. but the primaty key cannot be turnd off just the columns that has been idexed.

On mysql.com there is
USE INDEX, IGNORE INDEX, FORCE INDEX

0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19593001
> On mysql.com there is USE INDEX, IGNORE INDEX, FORCE INDEX

These are only to be used together with the SELECT statement. You can force a select to use a certain index. For activating/deactivating indexes you have to use DISABLE/ENABLE KEYS.

Ad yes, your are correct. This only works for non-unique indexes. For PRIMARY KEY deactivating, there is not other options except to DROP it and create it again when you're done.
0
 
LVL 1

Author Comment

by:AWestEng
ID: 19593092
Oki so DISABLE/ENABLE KEYS if I understand you correct dont mess with the Primary keys just the index?
0
 
LVL 17

Expert Comment

by:Aleksandar Bradarić
ID: 19593401
Correct.

0
 
LVL 1

Author Comment

by:AWestEng
ID: 19593574
thx m8 :)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.
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
Course of the Month15 days, 3 hours left to enroll

839 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