Can you index a varchar

Where it can become an issue is when you have the varchar column as an FK in a billion row table. Your tables are quite small and the performance could be related to the OR clause. Unfortunately, the same issue applies no matter how you structure the query and I'm not familiar enough with PostgresSQL to offer much sorry. Try getting rid of the "OR e2. If it runs faster you may be able to run it quicker with a "union all". Sign up to join this community. The best answers are voted up and rise to the top.

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Learn more. Ask Question. Asked 10 years, 9 months ago. Active 9 years, 1 month ago. Viewed 42k times.

We're using PostgreSQL v8. With this current query and index, the response time of 14 seconds is reasonable or is there any scope for further tuning?

Improve this question. Community Bot 1. Gnanam Gnanam 2 2 gold badges 8 8 silver badges 16 16 bronze badges. Before I use your suggestion to produce a 1M times 1M cartesian product that will consume all of memory, my CPU, and a hard drive, I'd appreciate an explanation of what your suggestion is designed to test. If your suggestion is a DBA joke, I got it. Ha ha. Very funny. Isn't there another section of this forum for clowns?

Grant Fritchey. As a rule, you want indexes to be as narrow as possible and as selective as possible. So the best index is a unique integer value. However, the real world intrudes on that a bit. Heck yes, index varchar. I don't know anyone dealing with real systems that doesn't index varchar.

Theodore Roosevelt. J-F Bergeron. It will create a table of 1M rows that you can use to test, with a varchar of 36, that you can index and see its behavior. Now, that looks pretty ironic that you insult one of the best posters on SSC saying he gave you a clown suggestion. Maybe you did not understand what he was trying to help you with, then all you need to do is kindly ask questions.

People here are just trying to help, and I find it really immature that you answer that way when someone is trying to help you, and teach you indexing. Then, add a nonclustered index on it, and do the same query, it will show you how fast it will now be. Depending on how large the varchar columns are you may get quicker results using Full-Text Indexes on these columns. If the text columns are not to large, for example a persons first name then i would be more inclined to use a traditional non-clustered index.

Like a lot of things in SQL it can really depend on your data and what results you are trying to achieve as Jeff suggested sometimes it can be better to try out the scenarios with some test data to find the optimal solution. If you don't believe me, look it up in Books Online. Ian Scarlett. See more: SQL. Can someone help me in understanding, when to create index on varchar,when do we use it?

Is it best practise or not What I have tried: Same as above. Posted Nov am Member Updated Nov am Afzaal Ahmad Zeeshan. Add a Solution. Top Rated Most Recent. Accept Solution Reject Solution.

Hi, You can create index on varchar, of course, especially on fields that are supposed are going to be filtered frequently in your application. But the most important think is to choose correcty the fields for creating the index. You should take the most selective possible fields. If not, indexes might not be used, it will depend on "statistics", but the probability will be low.

The same to create primary keys To conclude: 1 You can also create indexes on multiple fields combining int and varchar types, taking special care for filtering always on first field defined in the index.

If you just filter using the second field index will not be used. I mean, use only fields you need in your final result set. If all columns can be returned from the index the query will be "covered" and this will be great for the perfonmance. So, consider including new "aditional columns" to store in the index if possible according to your application requirements.

Posted Nov am jamuro Member Nov pm. Indexes can be "unique" or "not unique".