Return to Revisions

• First, I suggest you add two separate indexes, on

    (InsideProtocolKey, InsideIpHex) INCLUDE (RowKey)
  
    (OutsideProtocolKey, OutsideIpHex) INCLUDE (RowKey)
  

  and try the queries again. Your 4-column index is not good for the "Outside" query as the columns appear in the 2nd and 4th position and only slightly good for the "inside" query (1st and 3rd). Plus, these 2 indexes will be half in size (20 bytes vs 40 bytes per row).

• Second, a minor improvement. Since you only have two options for the ProtocolKey column (and its variations, Inside/Outside), you could conevert (all of them) from int (4 bytes) to tinyint (1 byte) or even to bit (1 bit) and save 3 bytes per row (or 3 + 7/8).

  It won't be a huge saving, but for big tables, it would help. For the not so big, 200M rows x 3 bytes = 600MB save, for every index where the columns appear.

Smaller indexes, smaller size on disk and more important, less memory and more probable to stay in memory, especially with the low RAM server you have.