Hi
I''m sorting some columns like this: MyModel.order("LOWER(column)
ASC")...
But these queries are quite slow. I''m on Postgres by the way.
Does Rails support creating a lowercase index for these situations? I know
Postgres has support for it and I guess I can create one like this (found
on SO):
execute "CREATE UNIQUE INDEX index_products_on_lower_name ON products USING
btree (lower(name));"
But does Rails have support for creating it? Don''t like to use
execute() if
there is a better way :)
Cheers,
Linus
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To view this discussion on the web visit
https://groups.google.com/d/msgid/rubyonrails-talk/bc408a32-a4ea-4561-a8d3-8bdffef291ca%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
On Tue, Jul 2, 2013 at 12:13 PM, Linus Pettersson <linus.pettersson-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> execute "CREATE UNIQUE INDEX index_products_on_lower_name ON products USING > btree (lower(name));" > > But does Rails have support for creating it? Don''t like to use execute() if > there is a better way :)Not that I''ve ever seen but that''s expected tbh. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAM5XQnwj7gg0%3DxqXmNE1msHAER6iWmdFc2d9BHvqLD6tWRFHxQ%40mail.gmail.com. For more options, visit https://groups.google.com/groups/opt_out.
How about case insensetive collation for your columns? -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/f2e5a7d2-a351-4c50-9d9c-ce81111d56c7%40googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
Straight from rails4.0 documentation: http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html Creating an index with a specific method add_index(:developers, :name, using: ''btree'') generates: CREATE INDEX index_developers_on_name ON developers USING btree (name) -- PostgreSQLCREATE INDEX index_developers_on_name USING btree ON developers (name) -- MySQL Note: only supported by PostgreSQL and MySQL On Tuesday, July 2, 2013 1:13:15 PM UTC-4, Linus Pettersson wrote:> > Hi > > I''m sorting some columns like this: MyModel.order("LOWER(column) ASC")... > But these queries are quite slow. I''m on Postgres by the way. > > Does Rails support creating a lowercase index for these situations? I know > Postgres has support for it and I guess I can create one like this (found > on SO): > > execute "CREATE UNIQUE INDEX index_products_on_lower_name ON products USING btree (lower(name));" > > But does Rails have support for creating it? Don''t like to use execute() > if there is a better way :) > > Cheers, > Linus >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/322a103d-6306-42a1-91b9-8345a151af91%40googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
Direct from Rails4.0 ActiveRecord documentation: Creating an index with a specific method add_index(:developers, :name, using: ''btree'') generates: CREATE INDEX index_developers_on_name ON developers USING btree (name) -- PostgreSQLCREATE INDEX index_developers_on_name USING btree ON developers (name) -- MySQL Note: only supported by PostgreSQL and MySQL On Tuesday, July 2, 2013 1:13:15 PM UTC-4, Linus Pettersson wrote:> > Hi > > I''m sorting some columns like this: MyModel.order("LOWER(column) ASC")... > But these queries are quite slow. I''m on Postgres by the way. > > Does Rails support creating a lowercase index for these situations? I know > Postgres has support for it and I guess I can create one like this (found > on SO): > > execute "CREATE UNIQUE INDEX index_products_on_lower_name ON products USING btree (lower(name));" > > But does Rails have support for creating it? Don''t like to use execute() > if there is a better way :) > > Cheers, > Linus >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/c2f87267-c2ef-42ac-a66e-951ce8a566e5%40googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
I''m on Rails 3.2.13 I''m afraid. I''ll just use the execute statement for now :) Den onsdagen den 3:e juli 2013 kl. 20:30:38 UTC+2 skrev Rick:> > Direct from Rails4.0 ActiveRecord documentation: > > Creating an index with a specific method > > add_index(:developers, :name, using: ''btree'') > > generates: > > CREATE INDEX index_developers_on_name ON developers USING btree (name) -- PostgreSQLCREATE INDEX index_developers_on_name USING btree ON developers (name) -- MySQL > > Note: only supported by PostgreSQL and MySQL > > > On Tuesday, July 2, 2013 1:13:15 PM UTC-4, Linus Pettersson wrote: >> >> Hi >> >> I''m sorting some columns like this: MyModel.order("LOWER(column) ASC")... >> But these queries are quite slow. I''m on Postgres by the way. >> >> Does Rails support creating a lowercase index for these situations? I >> know Postgres has support for it and I guess I can create one like this >> (found on SO): >> >> execute "CREATE UNIQUE INDEX index_products_on_lower_name ON products USING btree (lower(name));" >> >> But does Rails have support for creating it? Don''t like to use execute() >> if there is a better way :) >> >> Cheers, >> Linus >> >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/5586011a-477b-40e0-ace4-a134ec02f036%40googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
That doesn''t address the lower(name) portion of the question, just the part where it specifies btree. Any idea how to do the lowercase portion? On Wednesday, July 3, 2013 2:30:38 PM UTC-4, Rick wrote:> > Direct from Rails4.0 ActiveRecord documentation: > > Creating an index with a specific method > > add_index(:developers, :name, using: ''btree'') > > generates: > > CREATE INDEX index_developers_on_name ON developers USING btree (name) -- PostgreSQLCREATE INDEX index_developers_on_name USING btree ON developers (name) -- MySQL > > Note: only supported by PostgreSQL and MySQL > > > On Tuesday, July 2, 2013 1:13:15 PM UTC-4, Linus Pettersson wrote: >> >> Hi >> >> I''m sorting some columns like this: MyModel.order("LOWER(column) ASC")... >> But these queries are quite slow. I''m on Postgres by the way. >> >> Does Rails support creating a lowercase index for these situations? I >> know Postgres has support for it and I guess I can create one like this >> (found on SO): >> >> execute "CREATE UNIQUE INDEX index_products_on_lower_name ON products USING btree (lower(name));" >> >> But does Rails have support for creating it? Don''t like to use execute() >> if there is a better way :) >> >> Cheers, >> Linus >> >-- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/7e793c66-22d6-4270-86c3-580ed226e719%40googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
For PostgreSQL: create index index_developers_on_name on developers((lower(name))); Note the extra set of () to indicate that lower is a functional expression instead of a column name. On Aug 14, 2013, at 6:46 AM, Jonathan Dean <jonathanedean-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> That doesn''t address the lower(name) portion of the question, just the part where it specifies btree. Any idea how to do the lowercase portion? > > On Wednesday, July 3, 2013 2:30:38 PM UTC-4, Rick wrote: > Direct from Rails4.0 ActiveRecord documentation: > > Creating an index with a specific method > > add_index(:developers, :name, using: ''btree'') > generates: > > CREATE INDEX index_developers_on_name ON developers USING btree (name) -- PostgreSQL > CREATE INDEX index_developers_on_name USING btree ON developers (name) -- MySQL > Note: only supported by PostgreSQL and MySQL > > > > On Tuesday, July 2, 2013 1:13:15 PM UTC-4, Linus Pettersson wrote: > Hi > > I''m sorting some columns like this: MyModel.order("LOWER(column) ASC")... But these queries are quite slow. I''m on Postgres by the way. > > Does Rails support creating a lowercase index for these situations? I know Postgres has support for it and I guess I can create one like this (found on SO): > execute "CREATE UNIQUE INDEX index_products_on_lower_name ON products USING btree (lower(name));" > But does Rails have support for creating it? Don''t like to use execute() if there is a better way :) > > Cheers, > Linus > > -- > You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. > To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/7e793c66-22d6-4270-86c3-580ed226e719%40googlegroups.com. > For more options, visit https://groups.google.com/groups/opt_out.-- Scott Ribe scott_ribe-ZCQMRMivIIdUL8GK/JU1Wg@public.gmane.org http://www.elevated-dev.com/ (303) 722-0567 voice -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/5EEA25C9-6D77-43B2-A9B1-7853ED664509%40elevated-dev.com. For more options, visit https://groups.google.com/groups/opt_out.