[MySQL] Fastest way to Search from a Table

[Tharabar]

Can someone suggest me.. ?

To make a keyword search from a specific field from a Table that have
millions of rows.
I used normal SQL query as follow

SELECT `field1`,`field2`,`field3` FROM 'table' WHERE `field5` LIKE
'%keyword%1' AND `field5` LIKE '%keyword2%'

(Field 5 is text and not an index field)

Now my table have 17 + and it took 3-7 second to process such query.

Many Thanks.

z.t.wai

Re: [MySQL] Fastest way to Search from a Table

[Alex Arul Lurthu]

On 8/7/07, Tharabar [EMAIL PROTECTED] wrote:

 SELECT `field1`,`field2`,`field3` FROM 'table' WHERE `field5` LIKE
 '%keyword%1' AND `field5` LIKE '%keyword2%'


The most trivial way to speedup a query is to add an index on the columns
that are being used in the where clause. But doing 'LIKE' operation with the
pattern beginning with '%' it renders the index use less.

If you use MyISAM you can always use full text search on a text field for
such use cases.


-- 
Thanks
Alex
http://alexlurthu.wordpress.com