MySQL Forums
Forum List  »  Performance

Re: Multi-column index issue?
Posted by: Sergey Petrunya
Date: April 27, 2005 11:47AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
3143
April 27, 2005 06:21AM
1988
April 27, 2005 06:39AM
2191
April 27, 2005 06:47AM
1940
April 27, 2005 07:19AM
2113
April 27, 2005 07:34AM
Re: Multi-column index issue?
3348
April 27, 2005 11:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.