How does one do that? How do you drop auto-increment attribute then drop the
index then restart the auto increment value where it was before you dropped
it?? I did not know you could do that.
The reason I ask is because the dbf_UID is a unique id tag the coders use to
identify a product by
Hello Nunzio,
Instead of Dropping a index, you can disable the indexes and get the work
done and re-enable them.
If you are ok with this then run the below as a shell script :-
MUSER=username
MPASS=password
DATABASE=dbname
for db in $DATABASE
do
echo starting disabling indexes for database --
At 01:06 AM 8/10/2010, you wrote:
Hello Nunzio,
Instead of Dropping a index, you can disable the indexes and get the work
done and re-enable them.
Disabling keys will NOT disable Primary or Unique keys. They will still be
active.
Mike
If you are ok with this then run the below as a
Thanks for the feedback. What I am trying to do is two things:
1. Remove all indexes and make the database smaller to copy and move to another
prod box. Currently my indexes are in the double digit GB! Yikes ;-)
2. Remove all indexes so I can find out which ones are needed then tell mysql
to
This should give you a good starting point (not tested):
select distinct concat('ALTER TABLE ', TABLE_NAME, ' DROP INDEX ',
CONSTRAINT_NAME,';')
from information_schema.key_column_usage where
TABLE_SCHEMA='mydatabase';
- md
On Tue, Aug 10, 2010 at 10:43 AM, Nunzio Daveri
Hello Michael, thanks for the one liner. I ran it BUT I started to get errors
after I ran it the first time, this is what I got the 2nd time I ran it (first
time I ran it I had 63 rows in the query, the 2nd time I have 9). I ran it
twice to make sure it got rid of the indexed. I verified the
It's not a completely solution and will need some tweaking.. You
might have to run the PRIMARY KEYS distinctly from the rest.
- michael dykman
On Tue, Aug 10, 2010 at 4:43 PM, Nunzio Daveri nunziodav...@yahoo.com wrote:
Hello Michael, thanks for the one liner. I ran it BUT I started to get
Hi Micheal and all, ok so I did some digging around and I still can't find why
I
cant drop the last few indexes.
mysql SELECT COUNT(1) FROM INFORMATION_SCHEMA.STATISTICS WHERE table_schema =
'db_Market' AND table_name = 'dbt_Fruit' and index_name = 'PRIMARY';
+--+
| COUNT(1) |
auto_increment is only allowed on primary-keyed columns. I expect it
is not allowing you to drop the primary key because that column has
the auto_increment attribute. Drop that manually, and the primary key
should be able to let go.
- md
On Tue, Aug 10, 2010 at 5:58 PM, Nunzio Daveri
Hello Gurus, is there a way / script that will let me DROP ALL the indexes in a
single database? for example, lets say my database is call db_Animals, and
inside db_Animals there are 97 tables, is there a SINGLE command or a perl
script of some kind that can read all the MYI files, remove the
10 matches
Mail list logo