fast DEL when using LIKE mysql

Anon

i have a database of about 80 million items.. and some times i have to do a remove using "LIKE" eg

delete from others where (name like "%.1bigred.com");

and it takes forever! like over 5min.. i have about 1700 words to run thru, the system is a 1Gz with 512MB ram, running only apache and Mysql, how can i speed this up?

kparker

Hey, it's not really forever, but only about 6 days!

Seriously, if the only way you have to locate the rows is by a LIKE search with a leading wildcard, you are probably Out Of Luck. Is this a one-time thing? If not, you should think long and hard about how to structure things differently so you can find items without the wildcard search. Even adding full-text indexing might give better results.

Anon

arg, well thats piss poor, i mean.. i have ran into so many problems, MySql is not made for large databases at all. MS-SQL is no better what the heck to yahoo and webcrawler do when they have to index a couple billion web pages!

kparker

Actually, mysql can do pretty well with large database, as long as the ratio of reading to writing is high. But what you're asking it to do here does not lend itself to speed: you're asking it to sequentially scan every single row in the entire table (of x million rows) in case the trailing part of the string happens to match your pattern.

This is why I suggested that a redesign of the database might help if this is going to be a common operation. For example, if the table in question contained, not a literal string, but a numeric reference to the ID column of a separate name table, you might speed things up by an order of magnitude. In other words, instead of

  create table others
  (
  ...
  name text
 &nbsp😉;

you would have:

  create table names
  (
  id int auto_increment,
  name text
 &nbsp😉;

  create table others
  (
  ...
  name_id int // references names.id
 &nbsp😉;

  delete from others where others.name_id = names.id
    and names.name like '...';

This only makes sense if 'others' has a lot more rows than 'names'--i.e. if the same name appears frequently in 'others'. If not, it's back to the drawing board.