Odd MySQL results with ORDER BY and indexes

I had a slow query to sort out so added missing indexes to surname and first_names to improve. I think this is when the behaviour changed.

The following query now incorrectly returns all rows, ie, including people who aren’t fred (187,000):

SELECT * FROM `burials` WHERE (surname like ‘fred%’ or first_name like ‘fred%’) ORDER BY surname ASC

This query, without the ORDER BY, returns 3,002 rows (about the right number):

SELECT * FROM `burials` WHERE (surname like ‘fred%’ or first_name like ‘fred%’)

Not sure what to do here as my app’s broken but I don’t think it’s a logic problem. My local dev database (on older MySQL 5.0.67) does not have this behaviour.

Any ideas?

(Fortunately the live query has a LIMIT.)

Thanks,
Ian

Hi Ian,

I’ve tried this query on your db and it seems to be ok now. The query returns 3,002 rows with or without the surname. I can see that the table still has the indexes, so I’m assuming this was another problem of some kind that you’ve since solved? It’s not something I’ve ever seen MySQL do before – how did you solve it?

As a side note, MySql can actually usually only use one index per table per query, so you usually need a composite (multi-column) index. But in this particular case it’s able to do an index merge so you’re fine (you can use the EXPLAIN command to see what indexes MySQL will use for a particular query).

Thanks,

John.

Hi John,

Thanks for looking into it and for the extra info. The underlying issue isn’t resolved though. I have gotten around it by putting in a superfluous condition (cemetery_id < 1000, also indexed) that I know always evaluates to true but MySQL needs to check so app is working (the main thing).

I have run the query direct from the mysql client on my brightbox and still get odd results (showing the first row of 30):

SELECT * FROM burials WHERE (surname LIKE ‘fred%’ or first_name LIKE ‘fred%’) ORDER BY surname ASC LIMIT 0,30;
-————————--——————————-—————--———————-————--——————-———-—————————————————————————--———————————————————--————-
| id | cemetery_id | filename | surname | first_name | section | row | number | age | address | created_at | updated_at | interred |
-————————--——————————-—————--———————-————--——————-———-—————————————————————————--———————————————————--————-
| 89131 | 2 | Barnsley INDEX N-Z.csv | NADIN | William | H | | 417 | 53 | Station Lane, Summer Lane | 2009-07-15 16:47:28 | 2009-07-15 16:47:28 | 1870-04-19 |

Thanks,
Ian

Hi Ian,

ah yes, I’ve managed to reproduce it. Looks like a MySQL bug – I can’t find the exact bug reported, but some similar ones. We’ll be upgrading MySQL as part of some routine maintenance in a few weeks, so this may be fixed then.

The reason adding the extra condition on cemetery_id fixes it is because MySQL then chooses to use the cemetery_id index. I’d recommend removing the firm_name and surname indexes for now (perhaps replace them with one multi-column index (known as a composite index) and see if that helps)

John.