0

I have a table with 3 million rows and 6 columns. The problem is that my mysqld server wouldn't generate the output for any query and it would simply time out.
I then read here that over-indexing could involve too much of swapping data from memory to disk and can cause the server to slow down.
So I ran a query ALTER TABLE <Tbl_name> DROP INDEX <Index_name>;. This query has been running for 10 hours and has not completed yet.

  1. Is this expected to run for so long?
  2. Is there a better way to Dropping/Altering my indices?

edit - Added SHOW CREATE TABLE output

| Sample | CREATE TABLE `sample` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `FiMD5` varchar(32) NOT NULL,
  `NoMD5` varchar(32) NOT NULL,
  `SeMD5` varchar(32) NOT NULL,
  `SeesMD5` varchar(32) NOT NULL,
  `ImMD5` varchar(32) NOT NULL,
  `Ovlay` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `FiMD5_3` (`FiMD5`),
  KEY `ID` (`ID`),
  KEY `ID_2` (`ID`),
  KEY `pIndex` (`FiMD5`),
  KEY `FiMD5_` (`FiMD5`,`NoMD5`)
) ENGINE=InnoDB AUTO_INCREMENT=3073630 DEFAULT CHARSET=latin1 |
Community
  • 1
  • 1
Lelouch Lamperouge
  • 8,171
  • 8
  • 49
  • 60
  • Show us what `SHOW CREATE ` outputs and what `EXPLAIN SELECT ...` outputs – Kermit Jan 31 '13 at 18:24
  • 4
    Please post your table definition. Three million rows doesn't sound like it should be such a huge problem. My guess is that after 10 hours, it's safe to conclude that something's broken (unless mysqld is running on an Atari or something). – Ted Hopp Jan 31 '13 at 18:26

1 Answers1

1

Perhaps do the following would be faster:

  1. SELECT ... INTO OUTFILE first
  2. Use TRUNCATE TABLE to delete everything
  3. Modify the table
  4. Use LOAD to restore the data

If step 2 takes too long, perhaps dropping the table and recreate it.

Ed Heal
  • 59,252
  • 17
  • 87
  • 127