I was wondering if anyone can give a direction on this sub-query issue. Do I must upgrade it to MySQL 4.1 alpha or is there an alternative on MySQL 4.0.13 as a workaround to sub-query
Thanks -- Asif Iqbal http://pgpkeys.mit.edu:11371/pks/lookup?op=get&search=0x8B686E08 There's no place like 127.0.0.1 ---------- Forwarded message ---------- Date: Tue, 16 Sep 2003 14:31:21 -0400 (EDT) From: Asif Iqbal <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: MySQL 4.1 .vs. MySQL 4.0.13 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
