This project is archived and is in readonly mode.
Patch to add index length support
Reported by Emili Parreño | February 2nd, 2009 @ 10:55 PM | in 2.3.10
This patch add support for index length in MySQL adapter. Define a index length is a common practice for avoiding large indexes data, and improving performance.
You can now define a length for you indexes:
add_index(:accounts, :name, :name => 'by_name', :limit => 10) generates CREATE INDEX by_name ON accounts(name(10))
add_index(:accounts, [:name, :surname], :name => 'by_name_surname', :limit => 10) generates CREATE INDEX by_name_surname ON accounts(name(10), surname(10))
add_index(:accounts, [:name, :surname], :name => 'by_name_surname', :limit => {:name => 10, :surname => 20}) generates CREATE INDEX by_name_surname ON accounts(name(10), surname(20))
Comments and changes to this ticket
-
Carlos Júnior (xjunior) February 3rd, 2009 @ 12:58 AM
Fine! but I would replace this:
add_index(:accounts, [:name, :surname], :name => 'by_name_surname', :limit => {:name => 10, :surname => 20})
by:
add_index(:accounts, {:name => 10, :surname => 20}, :name => 'by_name_surname')
which also cover the first situation (with a simple index):
add_index(:accounts, :name => 10, :name => 'by_name')
With this, I don't think that the :limit option is really needed.
-
Emili Parreño February 3rd, 2009 @ 06:28 AM
Hash doesn't preserve order, which is critical when declaring indices. Using limit parameter, I wanted to keep the syntax of the migrations when you specify a limit for a field.
-
Deleted User February 3rd, 2009 @ 11:42 AM
Very good idea!
query_reviewer is always complaining about long indices in my apps, I guess this way I could solve this by defining shorter indices
-
Amos King February 3rd, 2009 @ 03:34 PM
This is a great idea. Anything that can help speed things up a little is wonderful.
+1
-
José Valim February 4th, 2009 @ 03:29 PM
I'm posting +2 since it solve two problems, but you should also check #734. :)
Support to index length is really necessary. For example, let's suppose I want to slug a title. Since I don't want to index the whole title, I set it to 10 characters.
Nowadays, I have to execute SQL inside migrations. This would fix it, so +1.
But even if I say to myself "just add the index, forget the length", it wouldn't be possible because the title is probably bigger than the max index length in MySQL. This takes us to the second problem is solves:
If I insert an index with length inside my migrations using execute, when dumping the schema it dumps the index but not the length. So when I'm applying db:test:clone it will raise an SQL error, saying that the column can not be indexed because it's bigger than the max index length.
So +1 again.
I'm just doing this "essay", because Pratik refused the other ticket, which I hope not to happen again. :)
-
Emili Parreño February 4th, 2009 @ 11:23 PM
I updated the patch with schema dumper support, more tests and sqlite tests fixed.
Thanks for your comments.
-
Fernando Guillen February 7th, 2009 @ 04:42 PM
Good implementation, very userfull. Althought too much mysql dependent.
Please review the schema_statements.rb documentation between lines 262 and 278.
I see things like:
# add_index(:accounts, [:name, :surname], :name => 'by_name_surname', :limit => {:name => 10, :surname => 15}) # generates # CREATE INDEX by_name_surname ON accounts(name(10), surname(20)) # CREATE INDEX by_name_surname ON accounts(name(10), surname(15))
Regards
-
Pratik March 12th, 2009 @ 04:57 PM
- Assigned user set to Pratik
-
Jonathan del Strother March 17th, 2009 @ 08:09 PM
Looks promising. However - am I right in thinking that mysql is the only database that handles index lengths?
Correct me if I'm wrong, but pretty much everything else in schema_statements is database-agnostic, right? Would it be feasible to push any of this patch into the mysql adapter? Failing that, we probably ought to at least note that :limit will only be supported on mysql.
-
Emili Parreño March 26th, 2009 @ 12:18 PM
@jonathan this patch is only for mysql adapter, and only modifies the schema if you works with this adapter
-
Michael Koziarski June 9th, 2009 @ 09:33 AM
- Milestone changed from 2.x to 2.3.4
-
Emili Parreño June 26th, 2009 @ 09:12 AM
I published a plugin with this feature for use while we wait the inclusion in the core.
http://github.com/eparreno/mysql_index_length/
I have to revise the patch to add more tests. In a few days I'll send a new patch.
-
Jeremy Kemper September 11th, 2009 @ 11:04 PM
- Milestone changed from 2.3.4 to 2.3.6
[milestone:id#50064 bulk edit command]
-
Jeremy Kemper April 22nd, 2010 @ 08:29 PM
- State changed from new to open
- Assigned user changed from Pratik to Jeremy Kemper
Emili, could you update a patch against latest master? I'd like to get this in, as well as a backport to 2-3-stable.
-
Repository May 8th, 2010 @ 12:43 PM
- State changed from open to resolved
(from [3616141fa2d2f35675d5962a1b329c8c51a5e9a3]) Add index length support for MySQL [#1852 state:resolved]
Example:
add_index(:accounts, :name, :name => 'by_name', :length => 10) => CREATE INDEX by_name ON accounts(name(10))
add_index(:accounts, [:name, :surname], :name => 'by_name_surname', :length => {:name => 10, :surname => 15}) => CREATE INDEX by_name_surname ON accounts(name(10), surname(15))
Signed-off-by: Pratik Naik pratiknaik@gmail.com
http://github.com/rails/rails/commit/3616141fa2d2f35675d5962a1b329c... -
Repository May 8th, 2010 @ 12:43 PM
(from [5b95730edc33ee97f53da26a3868eb983305a771]) Add index length support for MySQL [#1852 state:resolved]
Example:
add_index(:accounts, :name, :name => 'by_name', :length => 10) => CREATE INDEX by_name ON accounts(name(10))
add_index(:accounts, [:name, :surname], :name => 'by_name_surname', :length => {:name => 10, :surname => 15}) => CREATE INDEX by_name_surname ON accounts(name(10), surname(15))
Signed-off-by: Pratik Naik pratiknaik@gmail.com
http://github.com/rails/rails/commit/5b95730edc33ee97f53da26a3868eb... -
Repository May 8th, 2010 @ 03:48 PM
- State changed from resolved to open
(from [6626833db13a69786f9f6cd56b9f53c4017c3e39]) Revert "Add index length support for MySQL [#1852 state:open]"
This commit breaks dumping a few tables, as the sessions table.
To reproduce, just create a new application and:rake db:sessions:create rake db:migrate rake db:test:prepare
And then look at the db/schema.rb file (ht: Sam Ruby).
This reverts commit 5b95730edc33ee97f53da26a3868eb983305a771.
http://github.com/rails/rails/commit/6626833db13a69786f9f6cd56b9f53... -
Repository May 8th, 2010 @ 09:56 PM
- State changed from open to resolved
(from [eababa35cf5917c4ebd3f48cb29b9a2f6d1db404]) Revert "Add index length support for MySQL [#1852 state:resolved]" (breaks the build)
This reverts commit 3616141fa2d2f35675d5962a1b329c8c51a5e9a3.
http://github.com/rails/rails/commit/eababa35cf5917c4ebd3f48cb29b9a... -
José Valim May 8th, 2010 @ 10:11 PM
- State changed from resolved to open
-
Repository May 9th, 2010 @ 12:50 PM
- State changed from open to resolved
(from [77adb4bc2019e3a5b64ae8678310d176e30834d0]) Revert "Revert "Add index length support for MySQL [#1852 state:resolved]" (breaks the build)"
This reverts commit eababa35cf5917c4ebd3f48cb29b9a2f6d1db404.
http://github.com/rails/rails/commit/77adb4bc2019e3a5b64ae8678310d1... -
Repository May 9th, 2010 @ 12:50 PM
- State changed from resolved to open
(from [8d2f6c16e381f5fff6d3f24f5c73a443577a1488]) Revert "Revert "Add index length support for MySQL [#1852 state:open]""
This reverts commit 6626833db13a69786f9f6cd56b9f53c4017c3e39.
http://github.com/rails/rails/commit/8d2f6c16e381f5fff6d3f24f5c73a4... -
Emili Parreño May 10th, 2010 @ 07:57 AM
wooooow! so , now what's exactly the situation? I tried length indexes in 2-3-stable vanilla app and seems to work properly (I see you've changed :limit for :length)
-
Rizwan Reza May 16th, 2010 @ 02:41 AM
- Tag changed from activerecord, migrations, patch, schema to activerecord, bugmash, migrations, patch, schema
-
Jeremy Kemper August 30th, 2010 @ 02:28 AM
- Milestone changed from 2.3.9 to 2.3.10
- Importance changed from to Medium
-
rails March 5th, 2011 @ 12:00 AM
This issue has been automatically marked as stale because it has not been commented on for at least three months.
The resources of the Rails core team are limited, and so we are asking for your help. If you can still reproduce this error on the 3-0-stable branch or on master, please reply with all of the information you have about it and add "[state:open]" to your comment. This will reopen the ticket for review. Likewise, if you feel that this is a very important feature for Rails to include, please reply with your explanation so we can consider it.
Thank you for all your contributions, and we hope you will understand this step to focus our efforts where they are most helpful.
-
rails March 5th, 2011 @ 12:00 AM
- State changed from open to stale
Create your profile
Help contribute to this project by taking a few moments to create your personal profile. Create your profile »
<h2 style="font-size: 14px">Tickets have moved to Github</h2>
The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>
People watching this ticket
- arash
- Carl Lerche
- DHH
- Emili Parreño
- Frederick Cheung
- Geoff Buesing
- Jeremy Kemper
- José Valim
- josh
- Manu Campos
- Marcel Molina
- Matt Aimonetti (mattetti)
- Matt Jones
- Michael Klishin (antares)
- Michael Koziarski
- Paul Horsfall
- Paweł Kondzior
- Pratik
- Repository
- Rick
- Rick Benavidez
- Santiago Pastorino
- Tarmo Tänav
- Tobias Lütke
- Tom Lea
- Yehuda Katz (wycats)
Attachments
Referenced by
- 399 add_index_with_key_lengths_in_mysql Duplicate of #1852, which is more complete & actively bei...
- 4319 MySQL index name is too long duplicate of #3508 which is duplicate to #1852 which is ....
- 3508 Limit MySQL index name length duplicate #1852
- 1852 Patch to add index length support (from [3616141fa2d2f35675d5962a1b329c8c51a5e9a3]) Add ind...
- 1852 Patch to add index length support (from [5b95730edc33ee97f53da26a3868eb983305a771]) Add ind...
- 1852 Patch to add index length support (from [6626833db13a69786f9f6cd56b9f53c4017c3e39]) Revert ...
- 1852 Patch to add index length support (from [eababa35cf5917c4ebd3f48cb29b9a2f6d1db404]) Revert ...
- 1852 Patch to add index length support (from [77adb4bc2019e3a5b64ae8678310d176e30834d0]) Revert ...
- 1852 Patch to add index length support (from [8d2f6c16e381f5fff6d3f24f5c73a443577a1488]) Revert ...