Ask HN: need your help optimizing substring MySQL query

Imagine we have this table with a million unique rows.

    | id |        url        |
    +----+-------------------+
    | 1  | google.com        |
    | 2  | yahoo.com         |
    | 3  | reddit.com        |
    | 4  | digg.com          |
    | ...| ...               |

I'm running queries like

    SELECT id,url 
    FROM table
    WHERE url LIKE '%hoo%'

When you have a million rows, this query is slow (around 2-5 sec depending on what you search for). I need to make it fast.

So far the fastest solution I found is to concatenate everything into one long string, and do regular sub-string searches in the app (not the DB). It's a horrible and ugly solution, and I have re-build that string every time we insert a new row.

So the string looks like

    1|google.com#2|yahoo.com#3|reddit.com|4#digg.com ...

It's pretty long, but substring searches on it are pretty fast. I get the same results in around 70-200ms.

I would prefer to find a non-ugly solution.

thanks!