MySql querying taking too long

Question

I have a table

CREATE TABLE temp (
    i1 DECIMAL(10, 5),
    i2 DECIMAL(10, 5),
    i3 DECIMAL(10, 5),
    i4 DECIMAL(10, 5),
    i5 DECIMAL(10, 5),
    i6 DECIMAL(10, 5),
    i7 DECIMAL(10, 5),
    i8 DECIMAL(10, 5),
    i9 DECIMAL(10, 5),
    o1 DECIMAL(10, 5),
    o2 DECIMAL(10, 5),
    o3 DECIMAL(10, 5),
    o4 DECIMAL(10, 5),
    o5 DECIMAL(10, 5),
    o6 DECIMAL(10, 5),
    o7 DECIMAL(10, 5),
    o8 DECIMAL(10, 5),
    o9 DECIMAL(10, 5)
); 
CREATE INDEX input_data_index
ON temp (i1, i2, i3, i4, i5, i6, i7, i8, i9);
CREATE INDEX test_index
ON temp (o1); 

I am trying to search through this table using this query:

SELECT * FROM temp t
INNER JOIN temp x
ON t.i1 = x.i1 
AND t.i2 = x.i2
AND t.i3 = x.i3
AND t.i4 = x.i4
AND t.i5 = x.i5
AND t.i6 = x.i6
AND t.i7 = x.i7
AND t.i8 = x.i8
AND t.i9 = x.i9
WHERE t.o1 != x.o1; 

The table contains 180,362 rows. If I take out the where clause it only takes 0.157 seconds to run however with the where clause it takes a really long time to run (in excess of 300 seconds) at which point I just cancel it.

Why does it take so long to run when that where clause is added? Do you have any suggestions on how I can speed this up?

EDIT:

When I run the explain statement using the original index I had I get: img

When I run the explain statement using the one suggest by @Simulant (adding o1 to the index) I get: img2

but it still takes a really long time for this query to execute as well.

Answer 1

If you want the values of o1, where the i values are the same, you might consider:

select i1, i2, i3, i4, i5, i6, i7, i8, i9,
       group_concat(o1)
from temp
group by i1, i2, i3, i4, i5, i6, i7, i8, i9;

This is not exactly the same result set, but it might satisfy your needs.