Hello, I know this is off-topic for this list but I know someone here may be able to help me out of a jam. I ran the following against a table: ALTER TABLE `products` ADD INDEX `idx_products_model` ( `products_model` ); Some things now appear broken but don't know if this was the culprit or not. If I can revert it back it its original state I may be able to determine if it's the cause of some new problems. I did make a back up of the entire database before I made the change on 05-06-06 which I could restore but would lose all data from then til now. Can this index be removed? If so, how? TIA
ALTER TABLE `products` DROP INDEX `idx_products_model`; you might consider running either mysqlcheck or mysqlisamchk first... http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html Be sure to read the docs about the differences. Don't just run one of these. Thomas E Dukes wrote:> > Can this index be removed? If so, how? >
Thomas E Dukes wrote:> I ran the following against a table: > > ALTER TABLE `products` ADD INDEX `idx_products_model` ( `products_model` ); > > > Can this index be removed? If so, how? >Maybe DROP INDEX `idx_products_model` or ALTER TABLE `products` DROP INDEX `idx_products_model` -- Alan Sparks, UNIX/Linux Systems Integration and Administration <asparks at doublesparks.net>
centos-bounces at centos.org <> scribbled on Tuesday, May 09, 2006 8:57 PM:>> -----Original Message----- >> From: centos-bounces at centos.org >> [mailto:centos-bounces at centos.org] On Behalf Of Alan Sparks >> Sent: Tuesday, May 09, 2006 9:37 PM >> To: CentOS mailing list >> Subject: Re: [CentOS] OT: MySQL Help >> >> Thomas E Dukes wrote: >>> I ran the following against a table: >>> >>> ALTER TABLE `products` ADD INDEX `idx_products_model` ( >>> `products_model` ); >>> >>> >>> Can this index be removed? If so, how? >>> >> >> Maybe >> DROP INDEX `idx_products_model` >> or >> ALTER TABLE `products` DROP INDEX `idx_products_model` > > Thanks for the help. Wasn't sure if 'DROP' was what I needed > to use. I'm not that MySQL savy. I was following a > suggestion to speed up a process. > The problems I'm having aren't in the same table and I was > was just trying to cover any changes since the problems began. > > I ran mysqlcheck on the database and got an OK on all tables. > Must be something else. > > Thanks!! > > >> >> -- >> Alan Sparks, UNIX/Linux Systems Integration and AdministrationAre you running mysql-4.x? If so, you might consider using the caching features of 4.x+ It has helped some of my mysql intensive sites quite a bit. Mike
----Original Message---- From: centos-bounces at centos.org [mailto:centos-bounces at centos.org] On Behalf Of Thomas E Dukes Sent: Tuesday, May 09, 2006 11:05 PM To: 'CentOS mailing list' Subject: RE: [CentOS] OT: MySQL Help :: -----Original Message----- :: From: centos-bounces at centos.org :: [mailto:centos-bounces at centos.org] On Behalf Of Mike Kercher :: Sent: Tuesday, May 09, 2006 11:17 PM :: To: CentOS mailing list :: Subject: RE: [CentOS] OT: MySQL Help :: :: centos-bounces at centos.org <> scribbled on Tuesday, May 09, 2006 8:57 :: PM: :: :::: -----Original Message----- :::: From: centos-bounces at centos.org :::: [mailto:centos-bounces at centos.org] On Behalf Of Alan Sparks :::: Sent: Tuesday, May 09, 2006 9:37 PM :::: To: CentOS mailing list :::: Subject: Re: [CentOS] OT: MySQL Help :::: :::: Thomas E Dukes wrote: ::::: I ran the following against a table: ::::: ::::: ALTER TABLE `products` ADD INDEX `idx_products_model` ( ::::: `products_model` ); ::::: ::::: ::::: Can this index be removed? If so, how? ::::: :::: :::: Maybe :::: DROP INDEX `idx_products_model` :::: or :::: ALTER TABLE `products` DROP INDEX `idx_products_model` ::: ::: Thanks for the help. Wasn't sure if 'DROP' was what I needed to ::: use. I'm not that MySQL savy. I was following a suggestion to ::: speed up a process. The problems I'm having aren't in the same ::: table and I was was just trying to cover any changes since the ::: problems began. ::: ::: I ran mysqlcheck on the database and got an OK on all tables. Must ::: be something else. ::: ::: Thanks!! ::: ::: :::: :::: -- :::: Alan Sparks, UNIX/Linux Systems Integration and Administration :: :: Are you running mysql-4.x? If so, you might consider using the :: caching features of 4.x+ It has helped some of my mysql intensive :: sites quite a bit. :: :: Mike : : Hello Mike, : : Yes, I'm still running 4.1.12-3 I was hoping to speed things up by : adding the index to that table. : : I'm not too mysql savy as I said before. How do I set up the caching? : : Thanks In your /etc/my.cnf under the [mysqld] section, add these lines: query-cache-type = 1 query-cache-size = 20M You can adjust the cache size based on your system. Save your changes and restart mysqld and test. Mike