Hi Guys I have a quick question on indexing multiple columns on a MySQL database. I have one database with 200 million records. On this database I only have one indexed column. Search results are provided in less than one second. Now, I want to make use of the same database and index 2 columns. I want my users to search on two terms. The problem is, when I index 2 columns (e.g: name and surname) the results is terribly slow - it takes up to 10 minutes and longer. (But, searching on name alone, it is fast.) What is the best way to index two fields and get fast results? I tried making use of fulltext indexing, indexing both fields together and individually. Nothing this far works.
Is the user searching on both the name and surname at the same time? If not, you want one index on name and another index on surname. An index on both works for WHERE name = 'xxx' AND surname = 'yyy'. If the search is on a single field, the index should be on a single field. (You can have multiple indexes.)
Yep, the user search on both the surname and town. I tried indexing both columns together, but still no luck.
Are you using = or like? Like is a sequential lookup - it doesn't use indexes. If you're using = for both of them, there's one index that uses both of them, and it's still slow, there's another problem, and we can't tell you how to fix it if we don't know what it is.