How can I decrease time opening myisam table in an "union all" of the same table??
- by parm.95
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"?
Thanks in advance!