The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Hybrid View

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:

PHP Code:

SELECT * FROM table WHERE area LIKE 'location%'

PHP Code:

SELECT * FROM table WHERE town 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.

So none of the following use an index and all of them are really slow:

PHP Code:

SELECT * FROM table WHERE (area or town) LIKE 'location%'

PHP Code:

SELECT * FROM table WHERE area LIKE 'location%' OR town LIKE 'location%'

PHP Code:

SELECT * FROM table FORCE INDEX(have tried all of them) WHERE area LIKE 'location%' OR town LIKE 'location%'

How can I use an index?
Is that the wrong approach - should I be creating another field/s and using them?