mcl
06-17-2008, 03:31
I am a total newbie to MySQL and I was wondering if there is any way I
can speed up searching with indexing.
I have a keywords field containing 'word1,word2,word3,word4'. When I
do my matching of keyword like '%word2%, it takes a long time as I
have over 2 million records in the table.
Is it possible to create indexes of each word and then somehow get the
select statement to use those indexes.
idx-word1 = keywordfield where word1 like "%word1%"
idx-word2 = keywordfield where word1 like "%word2%"
etc
select from table where records are in idx-word2
or would it be best to set up a keyword table with a linkfield id to
the record field id in the main table and refresh the keyword table
after every update.
I was hoping that by using built in indexing it would be quicker and
more efficient.
Any pointers or sites with examples would be gratefully appreciated.
Richard
can speed up searching with indexing.
I have a keywords field containing 'word1,word2,word3,word4'. When I
do my matching of keyword like '%word2%, it takes a long time as I
have over 2 million records in the table.
Is it possible to create indexes of each word and then somehow get the
select statement to use those indexes.
idx-word1 = keywordfield where word1 like "%word1%"
idx-word2 = keywordfield where word1 like "%word2%"
etc
select from table where records are in idx-word2
or would it be best to set up a keyword table with a linkfield id to
the record field id in the main table and refresh the keyword table
after every update.
I was hoping that by using built in indexing it would be quicker and
more efficient.
Any pointers or sites with examples would be gratefully appreciated.
Richard