how to use index on a table used for searching using regex or like '%example%'

I want to use index on table where i m searching开发者_JAVA百科 a string using regex '^searchkeywordname$|searchkeywordname' ,this is scanning whole table .how can i retrieve fast result using index or sumthing

---

Sure you can use regular expressions in your queries but I doubt if an index would help. Index will help with queries like:

... WHERE Keywords LIKE 'keyword'
... WHERE Keywords LIKE 'keyword%'
... WHERE Keywords IN ( 'a keyword', 'another keyword' )

Alternatively, you can create a FULLTEXT INDEX on your column/columns which will allow you to query the database in unusual but useful ways:

... WHERE MATCH ( Keywords ) AGAINST ( 'library in france' )
... // AFAIK it'll also match derivatives such as 'libraries', 'french'

---

You can't use sumthing - MySQL doesn't support that.

You also can't use a regular index on a lookups that are not anchored at the beginning, since any index lookup starts with tghe first character.

The best solution for you is to build a word index table (NOTE: nothing to do with database index) and search for individual keywords in it. As an alternative, look up full text indexes in MySQL: http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html