List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:September 13 2001 9:02am
Subject:Re: adding INSERT support to MERGE tables
View as plain text  
Hi!

>>>>> "Benjamin" == Benjamin Pflugmann <philemon@stripped> writes:

Benjamin> Hi!
Benjamin> On Wed, Sep 12, 2001 at 04:53:53PM +0300, monty@stripped wrote:
>> 
>> >>>>> "Benjamin" == Benjamin Pflugmann <philemon@stripped>
> writes:
>> 
Benjamin> Hello again.
Benjamin> I finally made a patch of my current working version. After I upgraded
Benjamin> from 3.23.40 to 3.23.42 I had problems compiling, though. I had to
Benjamin> change sql/gen_lex_hash.cc, but since I am not really knowing what I
Benjamin> am doing here, there is probably a better fix. I did the following
Benjamin> change (applied with patch -p1 -d your-mysql-src < patch-file):
Benjamin> [...]
>> 
>> Yes, you have to modify gen_lex_hash.cc to get the new keywords in.
>> I will look into into getting better numbers for the arrays on my machine.

Benjamin> Ah. Good. 

>> I will examine and apply this to 4.0 during the next few days.

Benjamin> You know, this is not what I intended as final version? The change to
Benjamin> .frm files is still missing (and some cleanup as I understand the
Benjamin> source a bitter now).

I understand that.

(You are at least missing only locking the first/last table)

>> I will then email you some comments about this.

Benjamin> Additionally, I yesterday made a real-world test and found that
Benjamin> REPLACE doesn't work as expected (if replacing an existing row):

Benjamin> - If the old row is in a different table that the inserts are made,
Benjamin>   one has to rows with same id afterwards.

Benjamin> - If the tables of the old row and the insert are the same, the new
Benjamin>   one doesn't replace the old one but on gets an "duplicate key"
Benjamin>   error.

The above happens of course because you can't guarantee that something
is UNIQUE over a set of tables, even if each table has a UNIQUE key.

For example, you can have an auto_increment key in each table, which
is not unique for the whole set.

Benjamin> Sigh.

I don't think that REPLACE is that import for merge tables.
It' would be trivial to disable REPLACE for this table type.

Benjamin> Another thing is, that on identical tables (the unique columns are
Benjamin> also unique for the MERGE table), EXPLAIN gives different results in
Benjamin> some cases, using filesort to satisfy an ORDER BY although it uses an
Benjamin> index to read the column in.

Benjamin> Well, some more things for me to investigate ;-)

Actually, for MERGE tables, a filesort will probably be more efficient
than using an index, especially when you are are not using LIMIT.

Benjamin> Now, how do we proceed most reasonably? Would it be better that I wait
Benjamin> (with changes) until you can say something about the original problem
Benjamin> with ALTER TABLE. And if not, are patches better against my former
Benjamin> patch or against the original?

I plan to look at your patch tonight;  Hope you can wait until
tomorrow regarding this..

My current plan is to add this to 4.0 and then let you backport it to
MySQL 3.23 for your own usage.

Any change you could experiment in the 4.0 tree ?
(We would have much easier to take your patches for the 4.0 tree...)

Regards,
Monty
Thread
adding INSERT support to MERGE tablesBenjamin Pflugmann17 Aug
  • Re: adding INSERT support to MERGE tablesSergei Golubchik17 Aug
    • Re: adding INSERT support to MERGE tablesMichael Widenius17 Aug
      • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann30 Aug
        • Re: adding INSERT support to MERGE tablesSergei Golubchik30 Aug
          • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann30 Aug
        • Re: adding INSERT support to MERGE tablesMichael Widenius30 Aug
          • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann30 Aug
            • Re: adding INSERT support to MERGE tablesMichael Widenius1 Sep
              • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann5 Sep
                • Re: adding INSERT support to MERGE tablesMichael Widenius6 Sep
                  • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann10 Sep
                    • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann11 Sep
                      • Re: adding INSERT support to MERGE tablesMichael Widenius12 Sep
                        • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann12 Sep
                          • Re: adding INSERT support to MERGE tablesMichael Widenius13 Sep
                            • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann18 Sep
                      • Re: adding INSERT support to MERGE tablesMichael Widenius22 Sep
                      • Re: adding INSERT support to MERGE tablesMichael Widenius24 Sep
                        • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann27 Sep
    • Re: adding INSERT support to MERGE tablesSergei Golubchik17 Aug
      • Re: adding INSERT support to MERGE tablesBenjamin Pflugmann17 Aug
      • Re: adding INSERT support to MERGE tablesMichael Widenius18 Aug
        • Re: adding INSERT support to MERGE tablesSergei Golubchik18 Aug
          • Re: adding INSERT support to MERGE tablesMichael Widenius20 Aug
RE: adding INSERT support to MERGE tablesThe Tilghman20 Aug