0

I working on database optimization where there is a bulk insert from .csv file (around 3800 records) at an interval of every 15 minutes.

For this, I'm running a mis.sql file through cron. This file contains Nine (09) mysql queries that performs duplicate removal from the table where bulk insert is targeted, Inner join Inserts, Deletes and Updates (ALTER, DELETE, INSERT & UPDATE).

Recently, a problem is being experienced with a query that runs just prior to the Bulk Insert query. The query is -

ALTER IGNORE TABLE pb ADD UNIQUE INDEX(hn, time);

ERROR 1069 (42000): Too many keys specified; max 64 keys allowed

On encountering above error, all the subsequent queries are being skipped. Then I checked table pb and found that there are 64 Unique Index Keys created with same cardinal value along with 02 Index Keys and 01 Primary Key.

While trying to remove one of the Unique Indexes, it's taking too much of time (almost 15 mins for 979,618 records), but at the end, it's not being removed.

Is there any solution to this problem?

Rajorshe Mistry
  • 41
  • 1
  • 1
  • 9

1 Answers1

0

The first thing: Why is there an ALTER TABLE command at all? New data should change the data not the database design. So while INSERT, UPDATE and DELETE are valid options in such a script ALTER TABLE doesn't belong there. Remove it.

As to deleting the index: That should only take a fraction of a second. There is nothing to build or rebuild, simply to remove.

DROP INDEX index_name ON tbl_name;

The only reason for this taking so long I can think of is that there isn't even a short time slice when no inserts, updates and deletes take place. So maybe you'll have to stop your job for a moment (or run it on an empty file), drop all those unnecessary indexes (only keep one), and start your job again.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Thanks Thorsten Kettner..! Even after executing the query i.e. DROP INDEX index_name ON tbl_name , it's taking huge time (around 10-15mins) and finally throwing an error. Index remains, couldn't be removed. The ALTER TABLE usage for duplicate removal, is referred from http://stackoverflow.com/a/14050607/3872641 – Rajorshe Mistry Jul 13 '15 at 07:55
  • A unique index doesn't *remove* duplicates.It doesn't *allow* them! So you define the table and the index *once*, and that's it. You don't do that again and again. From the moment on you've defined the index there can be no duplicates. You would use `INSERT ON DUPLICATE KEY UPDATE` or `INSERT IGNORE` to handle conflicts. As to dropping the index, I see no reason for this not working immediately. Here is an alternate syntax you can try, but I don't think it makes any difference: `ALTER TABLE table_name DROP INDEX index_name`. Make sure there are no actions on the table when dropping the index. – Thorsten Kettner Jul 13 '15 at 08:13
  • It's done....I removed all the unique indexes and set only one unique index...every time the cron runs the .sql file, it will also perform - ALTER TABLE `pb` DROP INDEX hn_tm_indx, ADD UNIQUE INDEX hn_tm_indx (hn, tm); – Rajorshe Mistry Jul 14 '15 at 04:18
  • I still don't know why you drop and create that index at all. You have the table with the index. The index averts duplicates. Then you drop the index. Why? You would only do this to *allow* duplicates! Maybe you want to import data allowing duplicates and then remove duplicates afterwards before creating the index anew? Otherwise it would make absolutely no sense to drop that index. So most likely you would do best to remove both the DROP INDEX and the CREATE INDEX statement from your script. – Thorsten Kettner Jul 14 '15 at 09:40
  • You are right...There's no meaning of dropping and adding Unique Indexes...Unique Index has already been created. So, finally removed the statements from the script... Thanks Thorsten Kettner...! – Rajorshe Mistry Jul 14 '15 at 10:00