Cool! Thanks a lot Shawn.
> No, but you can. Modify your scripts so that the word EXPLAIN is the first
> thing in each one then re-execute them. This will product the optimizer's
> execution plan for each query. The results of all of those EXPLAIN
> SELECT.... statements will give us the most information to work from.
>
> Thanks.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> "YL" <[EMAIL PROTECTED]> wrote on 10/19/2004 02:58:35 PM:
>
> > The following are the real tests but not the real logic i'll apply:-)
> >
> > i have 4 very simple script files below and like to show you the
> > performance differece
> >
> > tst0.sql:
> >
> > select t1.participation_id id, t1.owner_id from participation t1 where
> > (t1.participation_id in (24,469)) and
> > (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb =[^;]
> > *\\\\314\\\\346');
> >
> > tst1.sql:
> >
> > select t2.participation_id from participation t2 where
> > t2.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]
> > *\\\\222\\\\224';
> >
> > tst2.sql:
> >
> > select t1.participation_id id, t1.owner_id from participation t1 where
> > (t1.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]
> > *\\\\222\\\\224')
> > and (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb
> > =[^;]*\\\\314\\\\346');
> >
> > and finally
> >
> > tst.sql
> >
> > select t1.participation_id id, t1.owner_id from participation t1 where
> > (t1.participation_id in (select t2.participation_id from
> > participation t2 where
> > t2.property_dict regexp 'firstName = {([^=]*=){0,2}[^=]*gb =[^;]
> > *\\\\222\\\\224'))
> > and (t1.property_dict regexp 'lastName = {([^=]*=){0,2}[^=]*gb
> > =[^;]*\\\\314\\\\346');
> >
> > Now the performance comparison:
> >
> > mysql> source tst0.sql
> > +-----+----------+
> > | id | owner_id |
> > +-----+----------+
> > | 24 | 1 |
> > | 469 | 4 |
> > +-----+----------+
> > 2 rows in set (0.02 sec)
> >
> > mysql> source tst1.sql
> > +------------------+
> > | participation_id |
> > +------------------+
> > | 24 |
> > | 469 |
> > +------------------+
> > 2 rows in set (0.02 sec)
> >
> > mysql> source tst2.sql
> > +-----+----------+
> > | id | owner_id |
> > +-----+----------+
> > | 24 | 1 |
> > | 469 | 4 |
> > +-----+----------+
> > 2 rows in set (0.03 sec)
> >
> > mysql> source tst.sql
> > +-----+----------+
> > | id | owner_id |
> > +-----+----------+
> > | 24 | 1 |
> > | 469 | 4 |
> > +-----+----------+
> > 2 rows in set (30.45 sec)
> >
> > Basically this seems to me that the sql composite tst.sql is
> > terribly slow than the time needed for separate executions of
> > tst1.sql and tst0.sql. And best of all is tst2.sql.
> >
> > Can someone explain my results?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
