The problem with multicolumn index is that for query
SELECT result FROM test WHERE SGSN_IP_1<="1644167936" AND SGSN_IP_2>="1644167936"
the range scan will be performed using only condition
SGSN_IP_1 <= "1644167936"
That is, the condition on the second key part will not reduce number of index records that need to be scanned (http://dev.mysql.com/doc/mysql/en/range-access-multi-part.html has some clues why it is so).
In order to reduce number of index records scanned, you might consider adding a column SGSN_IP_2_NEG, such that for each row
SGSN_IP_2_NEG = - SGSN_IP_2,
adding an index on (SGSN_IP_1, SGSN_IP_2_NEG), and using queries with
SGSN_IP_1<="1644167936" AND SGSN_IP_2_NEG < -1644167936
This will produce a scan on
(SGSN_IP_1, SGSN_IP_2) < (1644167936, -1644167936)
which will examine fewer index records (but still more then the original condition).
The actual gain from this depends on data distribution and may vary.
Another possible option is to pull out the VARCHAR(255) field into a separate table and do a join.
Sergey Petrunia, Software Developer
MySQL AB, www.mysql.com
My blog:
http://s.petrunia.net/blog