I wonder if my user can follow section 1.7.4.1 in mysql.com for what he wants to
do without me upgrading the MySQL from 4.0.x to 4.1 alpha
This is what my user wants
[snip]
I believe I will need sub-selects for the following type of query. Subqueries
are not supported until MySQL 4.1.
I will need to do longest-match lookups in the DB to mimic the way that IP
routers do longest-match routing lookups.
In the most basic case, I will have the following table entries that I
need to do a bitwise-AND comparison on and output ONLY the most-specific
route match. There will be other columns, but this should suffice to
illustrate why sub-queries are required:
CREATE TABLE as_aggregate (
as_num INTEGER,
ipaddr_val INTEGER,
netmask_val INTEGER,
prefix_length SMALLINT);
CREATE TABLE static_route (
router VARCHAR(32),
ipaddr_val INTEGER,
netmask_val INTEGER,
prefix_length SMALLINT,
next_hop_val INTEGER);
And then I need rows matching only the most specific bitwise-AND between
the static_route table and the as_aggregate table:
SELECT sr.router,
sr.ipaddr_val,
sr.prefix_length
sr.OTHER_COLUMNS
aa.as_num,
aa.ipaddr_val,
aa.netmask_val,
aa.OTHER_COLUMNS
FROM as_aggregate aa,
(SELECT sr.router,
sr.ipaddr_val,
sr.prefix_length,
MAX(msaa.prefix_length) AS most_specific_length
FROM static_route sr,
as_aggregate msaa
WHERE sr.ipaddr_val & msaa.netmask_val =
msaa.ipaddr_val & msaa_netmask_val
GROUP BY sr.router,
sr.ipaddr_val,
sr.prefix_length )
WHERE sr.ipaddr_val & msaa.netmask_val =
msaa.ipaddr_val & sr.netmask_val
AND aa.prefix_length = most_specific_length;
-----------------------------------------------------------------
I would prefer not to create temporary tables because:
* this data will change a lot as I tweak it and poll routers over and
over
* the size of the temporary table will be O(routes * as_aggregate)
* I am going to index all the columns that do bitwise AND so that row
pairings can be made by consulting the index only. This should make
the most-specific comparison nice and fast.
Also, I may have to do two levels of most-specific indirection for complex
route resolution. This will further complicate temporary tables.
[/snip]
Now My question is if there any possible way I can stick to the current
installation MySQL 4.0.13 and have the user do his job with some workaround ?
Thanks for your suggestions
--
Asif Iqbal
http://pgpkeys.mit.edu:11371/pks/lookup?op=get&search=0x8B686E08
There's no place like 127.0.0.1
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
