Re: Index on integer or on string field

On 2015-05-15 18:31, Scott Marlowe wrote:
> On Fri, May 15, 2015 at 9:18 AM, Job <Job(at)colliniconsulting(dot)it> wrote:
>> i have a table of about 10 millions of records, with the index on a string field.
>> Actually is alphabetical; since queries are about 100/200 per seconds, i was looking for a better way to improve performance and reduce workload.
>>
>> The unique values, of that fields, are about the 50 (category name), and we could create a second table to codify, with numerical integer values, the 50 recurring names.
>>
>> Is index are integer and not characteral, performance are better and workload reduces?
>>
>> Is there any comparisons?
>
> Have you considered using a multi-column index here? if there's a more
> selective field you could index along with your rather non-selective
> one that might work better. But it's hard to tell without looking at
> you database usage etc.

To add to Scott suggestion, think about what columns you include in your
queries when you are looking for a unique row (what columns are on the right
hand side of the WHEN clause). This will help you narrow down what makes
unique conditions on your table, from which you can create a better index.

--
http://yves.zioup.com
gpg: 4096R/32B0F416