> AND Substring(a.mob, 1, 4) = b.mob_series
There's what is probably the major problem with your query: your join
condition. Indices (you *do* have them on your join fields, don't you ?)
only work on the entire field you've indexed.
Function indices are not supported in MySQL, so you'll have to add a field
that contains the correct substring. You can auto-fill that using a trigger.
Another - more cosmetic - change I'd recommend is using join syntax instead
of putting your join condition in the where clause. It may be mostly
equivalent today, but that way you clearly indicate to the parse what he's
supposed to join on. Statements also become a lot easier to read :-)
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel