From: Zardosht Kasheff Date: February 25 2010 7:31pm Subject: Re: question on HA_EXTRA_IGNORE_DUP_KEY List-Archive: http://lists.mysql.com/internals/37744 Message-Id: <2f9663ba1002251131w4175f59amd2a0c67a348af602@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Can you please elaborate on the problems? Normally, with Insert ignore, a disk seek is required to verify uniquness of the element (and thus the return of an error). If I do not need to return an error, the disk seek is unnecessary. It is a performance improvement. I realize there are the following downsides: - the estimate of the number of rows of the table may get inaccurate - the statement will not properly return the number of duplicates found Are there other downsides? -Zardosht On Thu, Feb 25, 2010 at 1:55 PM, Sergey Vojtovich wrote: > Hi Zardosht, > > I believe this is rather a fragile solution. I can think about problems > at least with counters, wrong auto_increment. Also triggers will get > processed for this non-inserted record. > > Just out of curiosity, what kind of optimization you're trying to > achieve? > > Regards, > Sergey > > On Wed, Feb 24, 2010 at 01:20:12PM -0500, Zardosht Kasheff wrote: >> Ok, so if I understand this correctly, the following if clause: >> >> thd->lex->ignore && thd->lex->duplicates =3D=3D SQL_ERROR >> >> if it evaluates to true, I can assume that the user is doing "insert ign= ore". >> >> How I plan to use this is as follows. Suppose I have a table, (a int, >> b int, primary key (a)), that has the element (1,1) inserted. >> >> Given the following statements: >> insert ignore into table values (2,2), (1,2); >> select * from table; >> >> The expected result of the select * is to return (1,1),(2,2). The >> insert will make two calls to handler::write_row. For (2,2), >> handler::write_row will insert the element, and return success. With >> other storage engines, for (1,2), handler::write_row will insert >> nothing and return HA_ERR_FOUND_DUPP_KEY. What my change will do is >> still insert nothing, but instead of returning HA_ERR_FOUND_DUPP_KEY, >> it will return success. >> >> I assume there is nothing wrong with this? >> >> Thanks >> -Zardosht >> >> On Wed, Feb 24, 2010 at 1:05 PM, Venu Kalyan wrote: >> > Hi ! >> > >> > On Tue, Feb 23, 2010 at 3:23 PM, Zardosht Kasheff = wrote: >> >> But does this distinguish the commands "insert ignore" and "insert on >> >> duplicate key update"? >> > >> > Actually its contrary and unknown behavior even though MySQL allows >> > the synax to use IGNORE and ON DUPLICATE KEY / REPLACE together in >> > single statement, and it should in general through an error when used >> > together as server does not which one to prefer. >> > >> > In this case (Ignore and ON DUPLICATE KEY together in single insert) >> > ON DUPLICATE KEY takes precedence in the code due to the following >> > condition.. >> > >> > if (duplic !=3D DUP_ERROR || ignore) >> > =A0 =A0 =A0table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); >> > >> > For your case; =A0you can validate this using : >> > >> > if (thd->lex->ignore && thd->lex->duplicates =3D=3D SQL_ERROR) { >> > =A0// safe to ignore my dups >> > } >> > >> > if (thd->lex->ignore && thd->lex->duplicates !=3D SQL_ERROR) { >> > // used ignore together with IGNORE and ON KEY DUPLICATE / REPLACE >> > statements, I will decide what needs to be done here >> > } >> > >> > Hope this helps. >> > >> > Thanks >> > Venu Anuganti >> > >> >> >> >> On Tue, Feb 23, 2010 at 6:20 PM, Venu Kalyan wrote= : >> >>> You should look for both sql_command type and lex->ignore value (bot= h are >> >>> part of thd->lex->sql_command and thd->lex->ignore) to determine if = the >> >>> storage engine can safely return success on dup cases. But remember,= doing >> >>> so will result in wrong stats (number of rows inserted/updated) >> >>> >> >>> Apart from insert/replace, load data also uses the same logic. >> >>> >> >>> On Tue, Feb 23, 2010 at 12:07 PM, Zardosht Kasheff >> >>> wrote: >> >>>> >> >>>> Hello, >> >>>> >> >>>> Does anyone have any guidance here? >> >>>> >> >>>> Thanks >> >>>> -Zardosht >> >>>> >> >>>> On Wed, Feb 17, 2010 at 3:47 PM, Zardosht Kasheff >> >>>> wrote: >> >>>> > So it seems that just looking at this flag will not work, because >> >>>> > INSERT IGNORE and INSERT...ON DUPLICATE KEY UPDATE both set this = flag. >> >>>> > >> >>>> > I guess what I am wondering is the following, is there any way fo= r the >> >>>> > handler to know for sure that the user has called "insert ignore"= , and >> >>>> > if so, would it be ok for the handler to return success even if >> >>>> > internally a duplicate key is found? >> >>>> > >> >>>> > Thanks >> >>>> > -Zardosht >> >>>> > >> >>>> > On Fri, Feb 12, 2010 at 5:08 PM, Zardosht Kasheff >> >>>> > wrote: >> >>>> >> Hello all, >> >>>> >> >> >>>> >> I am trying to implement an optimization in our storage engine i= f >> >>>> >> HA_EXTRA_IGNORE_DUP_KEY is set. Would it be ok if handler::write= _row >> >>>> >> returns success if this flag is set, the sql statement is NOT >> >>>> >> SQLCOM_REPLACE or SQLCOM_REPLACE_SELECT, and the row is not inse= rted >> >>>> >> if a duplicate is otherwise there. >> >>>> >> >> >>>> >> Basically, the question is if this flag is set, is it ok to not = return >> >>>> >> HA_ERR_FOUND_DUPP_KEY in the cases where the statement is not "r= eplace >> >>>> >> into". >> >>>> >> >> >>>> >> Thanks >> >>>> >> -Zardosht >> >>>> >> >> >>>> > >> >>>> >> >>>> -- >> >>>> MySQL Internals Mailing List >> >>>> For list archives: http://lists.mysql.com/internals >> >>>> To unsubscribe: =A0 =A0http://lists.mysql.com/internals?unsub=3Dmyd= b08@stripped >> >>>> >> >>> >> >>> >> >> >> > >> >> -- >> MySQL Internals Mailing List >> For list archives: http://lists.mysql.com/internals >> To unsubscribe: =A0 =A0http://lists.mysql.com/internals?unsub=3Dsvoj@sun= .com >> > > -- > Sergey Vojtovich > MySQL AB, Software Engineer > Izhevsk, Russia, www.mysql.com >