Following my original post, it was suggested to me that one of the following may give better performance:
- SELECT … UNION SELECT …
- Using a temporary table with an index.
Well, not so. I have added the above cases to my benchmarking script, and updated the graph as shown below.
SELECT … UNION gave all sorts of problems. Firstly, it broke at a query set size of 1000 with the error
Can't open file: './bench/test1.frm' (errno: 24)
After a bit of searching I found that the remedy for this was to increase the MySQL open_files_limit setting (was 1024, increased to 8192). This got it going again, only to fall over once more at a query set size of 10000, this time with the error
parser stack overflow near 'UNION SELECT ...
to which I could not find a solution. In any case, the performance as shown in the graph is closely tracking the exponential degradation of the SELECT + OR case. Conclusion: SELECT UNIONs are not suited for a large number of unions. Useful when merging the results of several different SELECT statements, though.
The addition of an index to the temporary table also had no appreciable effect in this test, probably because MySQL will use the index in the main table to search while scanning through the temporary table. Perhaps there might be an improvement for the case where the temporary table is larger than the main table – but that would imply duplicates in the temporary table.