Ruby on Rails | Screencasts | Download | Documentation | Weblog | Community | Source

Ticket #5630 (new enhancement)

Opened 2 years ago

Last modified 5 months ago

[PATCH] Add support for creating prefix indexes to MySQL adapter

Reported by: fastjames@gmail.com Assigned to: sara
Priority: low Milestone: 2.x
Component: ActiveRecord Version: 1.1.1
Severity: minor Keywords: index length
Cc:

Description

ActiveRecord doesn't currently allow users to specify a prefix length when creating an index on a MySQL column (see http://dev.mysql.com/doc/refman/5.0/en/indexes.html for an explanation of prefix indexes). Since a prefix length is required for indexes on TEXT and BLOB columns, this comes up more often than you'd think. NOTE: This should combine easily with Ticket #3371.

Attachments

mysql_prefix_indexes.diff (2.0 kB) - added by fastjames@gmail.com on 07/06/06 22:19:36.
Diff to allow prefix indexes to be specified in column names.
mysql_prefix_indexes_v2.diff (4.4 kB) - added by anonymous on 07/07/06 15:27:27.
Another method for specifying prefix length on indexes.
mysql_index_prefix.zip (2.1 kB) - added by y0gi on 02/21/08 09:43:40.
plugin containing previously attached diff

Change History

07/06/06 22:19:36 changed by fastjames@gmail.com

  • attachment mysql_prefix_indexes.diff added.

Diff to allow prefix indexes to be specified in column names.

07/07/06 00:55:38 changed by bitsweat

  • cc set to bitsweat.
  • keywords set to index length.
  • version set to 1.1.1.
  • milestone set to 1.x.

Prefer connection.add_index :comments, :body, :name => idx_name, :length => 200 so munging quote_column_name is unnecessary. Is prefix length similarly meaningful for the other dbs?

07/07/06 15:26:35 changed by fastjames@gmail.com

  • version deleted.
  • milestone deleted.

OK, that's reasonable. Based on my research, this prefix length feature appears to be unique to MySQL (looking at all DBs with AR connection adapters). I wanted to isolate the change to the MySQL-specific code, so that's why I chose to mess with quote_column_name (which is already DB-specific) instead of putting it into SchemaStatements#add_index. The other reason I took the easy way out is that I couldn't figure out how best to allow length designations for multi-column indexes. The easy way works fine (it's just an array of strings, after all) but allowing a :length option would make it into an array of... hmm. Maybe this would work: 

add_index(:comments, [{:name => :body, :length => 200}, {:name => :foo, :length => 50}])

I'm uploading a revised patch that implements the syntax described above. Feel free to rip it up and shout, "WRO-ONG, DO IT AGAIN" if it's still not Rails-y enough.

07/07/06 15:27:27 changed by anonymous

  • attachment mysql_prefix_indexes_v2.diff added.

Another method for specifying prefix length on indexes.

07/07/06 16:49:00 changed by anonymous

  • version set to 1.1.1.
  • milestone set to 1.x.

(in reply to: ↑ description ) 01/23/07 11:10:45 changed by jeronimo

I'd appreciate it if this patch makes it to the next release, as right now it is basically impossible to use indexes on TEXT columns in MySQL. Even using "execute" doesn't work, since the code that recreates the test database doesn't use the execute command, and will try (and fail) to do add the index without length. This results in only half of our scheme being created, which is fairly fatal for your tests...

02/10/07 09:13:12 changed by bitsweat

  • cc deleted.

Use :sql schema_format to work around such issues.

02/21/08 09:43:40 changed by y0gi

  • attachment mysql_index_prefix.zip added.

plugin containing previously attached diff

02/21/08 09:45:03 changed by y0gi

I ran into this problem also, since we don't seem to be moving any where I turned the already attached diff into a plugin.

Thanks