Your best bet would be to have the application which inserts this data transform the url to a domain before inserting, so you have the data you want.
Given your current setup, full table scans may be unavoidable, but you may be able to improve the situation. I suspect that with clever use of string functions <http://dev.mysql.com/doc/mysql/en/String_functions.html> we could dramatically reduce the number of such queries. If I understand you correctly, you want everything up to the first '.'. This should do the trick:
UPDATE url_cat SET domain = SUBSTRING_INDEX(domain, '.', 1);
That will replace domain with the contents of domain up to, but not including, the first '.' all in one pass. Yes, it's still a full table scan, but it's 1 full table scan.
Michael
Martin Rytz wrote:
Hi all
I have a problem with slow update queries like these (5 examples):
update url_cat set domain = '01net' where left( domain, instr( domain, '.' )
-1 ) = '01net';
update url_cat set domain = '1-meta' where left( domain, instr( domain, '.'
) -1 ) = '1-meta';
update url_cat set domain = '105' where left( domain, instr( domain, '.' )
-1 ) = '105';
update url_cat set domain = '123love' where left( domain, instr( domain, '.'
) -1 ) = '123love'; update url_cat set domain = 'google' where left( domain, instr( domain, '.'
) -1 ) = 'google'; before the update, the field domain contains 'google.com' or 'google.de' or
'google.ch' and after the update it contains only 'google'.
i have to make thousends of this updates an it takes a long time. the table
'url_cat' contains about 100'000 entries! an index would help, but mysql
does not use any static index. the index should contain the
where-condition... but this is not possible, because the lenght of the
condition differs?! does anybody have another idea?
thank you in advance.
martin
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
