How can I decrease time opening myisam table in an "union all" of the same table??

Question

I have a myisam table with 2.5M rows, I use an union all to get my results as following:

(SELECT t.id FROM t WHERE type=1 LIMIT 10)
UNION ALL
(SELECT t.id FROM t WHERE type=2 LIMIT 10)
...
UNION ALL
(SELECT t.id FROM t WHERE type=25 LIMIT 10)

the time to opening the table t is about 6ms.

With a single request:

SELECT t.id FROM t WHERE type=1 LIMIT 10

the time is about 1ms.

What I don't understand is why mysql spend more time to the same table in union all. It should recognize that is the same table and so just opening at the first union.

Does anybody can help me to decrease the time for opening table in a "union all"?

Answer 1

6ms is pretty fast. You're fetching 25 times as much data and it's only taking 6 times as long.

I'd stop worrying about it and find where the real bottlenecks in your application are. If 6ms per query is a problem you might want to try reducing the number of queries you are performing per second.