Hi
I have a user who needs to do the following
[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]
