Speed of query, Indexing problem with OR query
The question is how to speed up a select query that is looking for a value that is in one field or another - the problem seems to be that the indexes are not being used and that a table scan is being done every time. The exact problem and most of what I have tried to solve it is detailed here. I could really do with some help on this!!
I have two fields area and town and I need to search if the location is in either of these fields.
The way the data is structured is that if no area was specified then area contains the same information as town. I cannot change this data setup. But I could add an extra field to say if the two fields are the same or different.
The table is about 1million rows.
I have indexes for area,town and area+town.
A search to return all rows 'where area like location%'' takes .03secs, and the same to find all rows where it is in town. Using explain reveals that the index used is area+town in the first case and town in the second. Queries used are:
SELECT * FROM table WHERE area LIKE 'location%'
However when I search for all rows with location in area OR town using any of the following queries no index is used and the query takes 4+ seconds because it does a full table scan and does not use an index. This applies even using FORCE INDEX.
SELECT * FROM table WHERE town LIKE 'location%'
So none of the following use an index and all of them are really slow:
SELECT * FROM table WHERE (area or town) LIKE 'location%'
SELECT * FROM table WHERE area LIKE 'location%' OR town LIKE 'location%'
How can I use an index?
SELECT * FROM table FORCE INDEX(have tried all of them) WHERE area LIKE 'location%' OR town LIKE 'location%'
Is that the wrong approach - should I be creating another field/s and using them?
Any suggestions greatfully received.