Search for all rows in the database where the field is different from another field

Question

I have the following SQL query:

SELECT * FROM table WHERE field_1 <> field_2 

What is the best index structure used to make this query efficient: two indexes on field_1 and field_2, or one index that includes both fields?

EDIT: MySQL Database

Answer 1

If you have a colossal table, it is better to denormalize it and save the result filed1 <> field2 in a separate column and update it every time you insert / update the corresponding row

Answer 2

I suppose this may depend on which platform you are using, but there is definitely one index on MS SQL Server!

Answer 3

Indexes will not help you.

The data byte must scan the table as it compares two fields on the same row.

Answer 4

It depends on your database engine, but it's usually best to assume that the query will use only one index per table. This will mean that one index in both columns is likely to be the best.

However, the only way to find out is to populate the table with dummy data and try. Make sure that the dummy data is representative in terms of how it is distributed, for example, if 99% of the field2 values ​​are identical to each other, then this can reduce the index value.

Answer 5

Of course, I would try all three options, but I remember that you write every index with every insert / update. (so indexing both fields should be more beneficial in terms of compensating for negative effects on recording performance). Remember that it doesn’t have to be perfect, it has to be good enough to cope with system bandwidth without creating unacceptable UI performance delays.

What I will try first is the only index in the field that has a variety of values ​​... i.e. if there are 1000 different values ​​in field 1 and only 20 in field 2, then put the index in field1.

Answer 6

Here's a good article on indices and inequality:

http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/

alternatively, if your data is huge, you can use a trigger to mark another column of bits, indicating whether the columns match or not, and then search in that column. Of course, it all depends on your situation.