Using a Mysql Trigger to break down a URL Field and store it's parts in a MyISAM Table -

i have mysql myisam table stores url in url field. url scheme, subdomain, , host ( eg. http://www.site.com or https://site2.edu ).

i need store domain, subdomain, , scheme in own fields can index them , search them. it's slow system query on millions of rows.

• domain: site.com, site2.com
• subdomain: www, ''
• scheme: http, https

how create mysql trigger this? need extract domain, subdomain, , scheme url field every time updated , store them in respective fields ( eg. domain, subdomain, scheme ) these fields written mysql trigger.

i realize parsing urls non-trivial, i'm concerned setting trigger this. can make adjustments , tradeoffs on quality of url parsing in application.

delimiter //  drop trigger if exists url_trigger_before_insert //  create trigger url_trigger_before_insert  before insert on url_table  each row   begin      declare no_proto char;      set new.scheme = substring_index(new.url, ':', 1);       set @no_proto = replace(new.url,concat(new.scheme,'://'),'');       set @no_proto = substring_index(@no_proto, '/', 1);        set new.subdomain = substring_index(@no_proto, '.', 1);      set new.host = substring_index(@no_proto, '.', -2);   end //  drop trigger if exists url_trigger_before_update //  create trigger url_trigger_before_update  before update on url_table  each row   begin      declare no_proto char;      set new.scheme = substring_index(new.url, ':', 1);       set @no_proto = replace(new.url,concat(new.scheme,'://'),'');       set @no_proto = substring_index(@no_proto, '/', 1);        set new.subdomain = substring_index(@no_proto, '.', 1);      set new.host = substring_index(@no_proto, '.', -2);   end //   delimiter ;