Hi,
> Table handler for MERGE can use keys of underlying tables for
> key lookups, but in fact it never will, as SQL optimizer will never ask
> to. Specifying keys for MERGE table is for SQL optimizer to able to
> chose the best way to execute your SELECTs. No real keys are created,
> as MERGE handler ignores requests for key creation - but SQL optimizer
> doesn't know about MERGE handler specific - and it shouldn't.
>
> So, please, specify the same keys for MERGE table, you specified
> for underlying tables.
I have been wondering about the use of indexes with MERGE tables. I came to
the same conclusion as what you have just explained, but by trial and error.
I would like to say that I think the section in the manual on MERGE tables
could do with some improvement!
Anyway, while we're on the subject, I seem to be having some trouble with my
indexes on MERGE tables. I posted a message a while ago, but no-one seems to
have noticed it...
Basically, if I create my MERGE table with indexes on the same columns as
the underlying tables, I then get strange results from some queries. Below I
quote part of my original message that illustrates the problem.
> mysql> select count(*) from eventlog_36;
> +----------+
> | count(*) |
> +----------+
> | 389959 |
> +----------+
> 1 row in set (0.00 sec)
>
> mysql> select count(*) from eventlog_all where bannerid = 36;
> +----------+
> | count(*) |
> +----------+
> | 1 |
> +----------+
> 1 row in set (0.01 sec)
>
> (The table 'eventlog_36' is one of the tables mapped to 'eventlog_all' which
> holds only records with a 'bannerid' column value of 36.)
>
> They don't match, so it's clearly not correct! It seems as though it's not
> looking at the indexes of the mapped tables correctly, although I have no idea
> why!
Also, I get the following error when I try to alter the mapping of the MERGE
table:
> mysql> ALTER TABLE eventlog_all UNION=(eventlog_11, <..lots of tables...>,
> eventlog_88);
> ERROR 1031: Table handler for '#sql-1f1_ea6d9' doesn't have this option
My MERGE table seems to be completely screwed! Could you explain what's
going on here? Is it because I'm running only version 3.23.32 of MySQL? Do I
need to upgrade? Any assistance would be appreciated, as no-one else seems
to be able to help.
Regards,
------------------------------------------------
Basil Hussain ([EMAIL PROTECTED])
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
