Dear Don, KH Chiu, List,
P.S (Chiu): Proceed as is in INSERT VALUES (),()...N?
Oops! What about futured stored procedures in Mysql 5.x ? Anyway...
Yes, there is a problems with error handling and proceeding but when you
use INSERT ... VALUES (),().. (for example 5 rows and after 3th rows
mysql fail to proceed with last 2, these 3 rows will *not* be removed
from db i.e INSERT will succeed particualry.. is it a bug in mysql?)
Moreover in all packets (batch) operations (not only in database/mysql
sense) when some of queries fail..all other queries in the packet fails
too.
Of course I thought about problem "how to return multiple results" for
example in two or more selects... however I'm not familar with mysql
core.. :(
BUT if mysql team release "cache" for futured operations..no,no..
AMD,INTEL processors do exaly the same to speed up execution of CPU
operations...
So if application *tells* to mysql that it will proceed two select
queries to same table, mysql may "execute" second operation in advance:
FUTURE - imaginary SQL statement:
FUTURE select_1 * from table where..., select_2 * from table where...
So when Mysql execute select_1:
select * from table where...
it *may* proceed/cache select_2
Example:
$sth = $dbh->prepare("FUTURE select * from table where id=52 and
name='some', select * from table where id=23 and email='[EMAIL PROTECTED]'");
...
$sth->execute();
...
# Execute first query
$sth = $dbh->prepare("select * from table where id=52 and name='some'");
$sth->execute();
...
# Execute second query
$sth = $dbh->prepare("select * from table where id=23 and
email='[EMAIL PROTECTED]'");
$sth->execute();
So Mysql *may* optimize these two queries; to test conditions, but: to
execute them separately. Of course when error occure in first query
Mysql will *not* be able to optimize them...
Moreover, if Mysql proceed 1/2 of DB file with first query (for example
restricted with "LIMIT"), Mysql still may do a particular
cache/optimizations. Imaginary sql statement "FUTURE" may be used for
other optimizations too!
The main idea was: "In fact I'm not talking only about 'select'-s but
any tables *examination*/*traverse* (i.e where clauses etc..), so
queries like these could be also speed up"
I know you are a clever guys, so *Mysql* will obtain the MAXIMUM from
that idea ;-)
Here I will answare to question about multi-user nature of Mysql:
Due Mysql reads only once from database (hard disk) i.e. proceed two
queries (conditions) for single row read, there will *not* be penalty
for disk seeks/read, because when given row is proceeded it *should* be
read in memory the whole.
However in case when you apply "bulk update" to different tables the
things getting complicated :(, but with FUTURE-like statement there are
still possibilities for optimizations :)
>
> On 23-Feb-2003 Julian wrote:
> > Speed improvement with packet proceeding!?
> >
> > 1. Packet proceeding:
> >
> > I'm not quite sure is it possible with Mysql but it could be "easy"
to
>
> > be implemented.
> > For example:
> >
> > select * from table where id=52 and name='some'
> > select * from table where id=23 and email='[EMAIL PROTECTED]'
> >
> > these two queries select row(s) from table 'table' which means that
> > these two queries could be tested simultaneously, so database file
will
>
> > be proceed only once.
>
> What about the case where one (or both) selects fail?
>
> And how would the application tell that there were multiple
> rows where id=23 and email='[EMAIL PROTECTED]' ?
>
> If you *know* that these two rows exist and unique then:
>
> SELECT a.*,b.* from table as a, table as b
> WHERE a.id=52 and a.name='some' AND
> b.id=23 and b.email='[EMAIL PROTECTED]'
>
> would do the same thing.
>
> <snipage>
>
> >
> > In fact I'm not talking only about 'select'-s but any tables
> > examination/traverse (i.e where clauses etc..), so queries like these
>
> > could be also speed up:
> >
> > update table set data='test' where name='some'
> > select * from table where id=10
> >
>
> What would be the sensible error message if your update failed
> but your select succeeds ?
>
> What would be the expected value of mysql_numrows() ?
>
> And what if there are multiple rows where id=10 ?
>
> <snipage>
>
> > 2. Bulk update/delete etc..
> >
> > Take a look at this update query (not implemented.. yet!)
> > update table1 set column=value,... where clause limit #, update
table2
>
> > set ....
> > (or delete from table1 where clause limit rows, delete from table2
> where
> > clause...)
> >
>
> The same question: How would a program tell which statement failed?
>
> <snip again>
>
> > Have I a good point here? Any ideas and discussion about these
> > suggestions?
>
> You'll need to rethink the failure modes.
> What you're suggesting might be useful in certain special case(s)
> but badly fails the general case.
>
> Regards,
> --
> Don Read [EMAIL PROTECTED]
> -- It's always darkest before the dawn. So if you are going to
> steal the neighbor's newspaper, that's the time to do it.
> (53kr33t w0rdz: sql table query)
>
__________________________________________
12MB-POP3-WAP-SMS-AHTИCПAM--TOBA-E-mail.bG
------------------------------------------
HOB БEЗПЛATEH AДPEC - http://mail.bg/new/
------------------------------------------
---------------------------------------------------------------------
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
