-1

I'm trying to add indexes to 16 columns at a time, in migration:

add_index :billing_invoices, [:due_date, :flat_fee, :percentage_fee, :subtotal, 
:processing_fee, :total, :invoice_number, :attn, :street_line_1, :street_line_2,
:city, :state, :zip_code, :is_deactivated, :start_date, :end_date]

But, I get this error:

Mysql2::Error: Specified key was too long; max key length is 3072 bytes: CREATE UNIQUE INDEX i ON billing_invoices (due_date, flat_fee, percentage_fee, subtotal, processing_fee, total, invoice_number, attn, street_line_1, street_line_2, city, state, zip_code, is_deactivated, start_date, end_date)

So, I tried adding :unique => true, :name => 'indexes_billing_invoices', as it was suggested in a SO post:

add_index :billing_invoices, [:due_date, :flat_fee, :percentage_fee, :subtotal, 
:processing_fee, :total, :invoice_number, :attn, :street_line_1, :street_line_2,
:city, :state, :zip_code, :is_deactivated, :start_date, :end_date],
:unique => true, :name => 'indexes_billing_invoices'

But, still it throws the same error. Even I tried :name => 'i' to check, but the same error comes.

Eyeslandic
  • 14,553
  • 13
  • 41
  • 54
Abhi
  • 4,123
  • 6
  • 45
  • 77
  • Possible duplicate of [Mysql::Error: Specified key was too long; max key length is 1000 bytes](http://stackoverflow.com/questions/3489041/mysqlerror-specified-key-was-too-long-max-key-length-is-1000-bytes) – Eyeslandic May 15 '17 at 09:48
  • @Iceman I want to know the fix in rails. Is it possible? I can't ask everyone to manually fix their mysql after pulling my code. – Abhi May 15 '17 at 09:49
  • Here too: http://stackoverflow.com/questions/8746207/1071-specified-key-was-too-long-max-key-length-is-1000-bytes – Eyeslandic May 15 '17 at 09:52
  • Why are you trying to index ALL of those columns together? – Nigel Ren May 15 '17 at 10:03
  • @NigelRen Yes, I ended up adding indexes separately. – Abhi May 15 '17 at 10:05

1 Answers1

0

I find the problem was caused by two reasons:

1> Trying to index all the columns together.

2> Some columns were of type varchar(255).

My Fix

Column Structure

+--------------------+---------------+
| Field              | Type          |
+--------------------+---------------+
| due_date           | datetime      |
| flat_fee           | decimal(10,2) |
| percentage_fee     | decimal(10,2) |
| subtotal           | decimal(10,2) |
| processing_fee     | decimal(10,2) |
| total              | decimal(10,2) |
| invoice_number     | varchar(255)  |
| attn               | varchar(255)  |
| street_line_1      | varchar(255)  |
| street_line_2      | varchar(255)  |
| city               | varchar(255)  |
| state              | varchar(255)  |
| zip_code           | varchar(255)  |
| is_deactivated     | tinyint(1)    |
| start_date         | datetime      |
| end_date           | datetime      |
+--------------------+---------------+

I added index to all varchar columns separately as follows:

add_index :billing_invoices, [:due_date, :flat_fee, :percentage_fee, :subtotal,
  :processing_fee, :total, :is_deactivated, :start_date, :end_date
], unique => true, :name => 'indexes_date_decimal_columns'

add_index :billing_invoices, :invoice_number
add_index :billing_invoices, :attn
add_index :billing_invoices, :street_line_1
add_index :billing_invoices, :street_line_2
add_index :billing_invoices, :city
add_index :billing_invoices, :state
add_index :billing_invoices, :zip_code

And it worked for me !!!

I adopted this fix as I wanted the fix only in rails

Abhi
  • 4,123
  • 6
  • 45
  • 77
  • Ok, great you got it working, but it is not a unique index on all columns now. Do you really need an index on all the columns? Adding a ton of indexes is counterproductive. – Eyeslandic May 15 '17 at 10:18
  • I understand your suggestion, but I see these columns are used heavily, so I thought of adding indexes to them all, as it's slowing down the billing page – Abhi May 15 '17 at 10:24
  • 1
    Yeah, I understand your concern. I've just done this mistake myself of adding too many indexes, it bulks up the table metadata and also slows down writing. But of course I don't know your schema and db, just wanted to point it out. Good luck. – Eyeslandic May 15 '17 at 10:26