Thunderstone Support Forums

I have a column (RemoteAddr) containing IP addresses which I want to index so I can query on certain ranges with REX expressions. AccessDate is a Texis date column. Here is my code to add the expressions and create the index:

<sql "set addexp = '>>152\.3\.=\digit{1,3}\.=\digit{1,3}'"></sql>
<sql "set addexp = '>>152\.16\.=\digit{1,3}\.=\digit{1,3}'"></sql>
<sql "set addexp = '>>152\.16\.19[1-4]\.=\digit{1,3}'"></sql>
<sql "set delexp = '\alnum{2,99}'"></sql>
<sql "create metamorph index xm_dbusage on dbusage (RemoteAddr, AccessDate)"></sql>

Here is my query:

<sql "select count(*) libhits from dbusage where (RemoteAddr like '/>>152\.16\.19[1-4]\.=\digit{1,3}' and AccessDate between $firstdate and $lastdate)">
</sql>

The result value of $libhits is (incorrectly) 0. This is the error:

<!-- 115 /searchdb/ejdb/dbusage_work:94: Query '/>>152\.16\.19[1-4]\.=\digit{1,3}' would require post-processing -->
<!-- 115 /searchdb/ejdb/dbusage_work:94: Query '/>>152\.16\.19[1-4]\.=\digit{1,3}' would require linear search -->

Another query that produces the same error:

<sql "select count(*) dukehits from dbusage where ((RemoteAddr like '/>>152\.3\.=\digit{1,3}\.=\digit{1-3}' or RemoteAddr like '/>>152\.16\.=\digit{1,3}\.=\digit{1-3}') and AccessDate between $firstdate and $lastdate)">
</sql>

What am I doing wrong? Is there a way to list the expressions for an index to check that the ones I added are actually in there?

Thanks,
David

Texis Web Script (Vortex) Copyright (c) 1996-2000 Thunderstone - EPI, Inc.
Commercial Version 3.01.959738425 of May 30, 2000 (sparc-sun-solaris2.5.1)

You might want to reread the manual section about addexp and delexp. That's not what they're for and your delexp usage is incorrect.

Rex searches within like always require linear postprocessing regardless of index expressions. Since your field is always an IP address and you're just doing prefix comparison create a regular index on the field and use matches
RemoteAddr matches '152.3.%' or RemoteAddr matches '152.16.%'

If you really wanted a metamorph index for some reason you could use an addexp expression like
\digit{1,3}\.=\digit{1,3}\.=\digit{1,3}\.=\digit{1,3}
then do wildcard searches like
RemoteAddr like '152.3.*'

OK. I thought I could make things more efficient by using a rex expression for the IP ranges 152.16.191-194.*, which you can only match with the union of four MATCHES clauses.