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?
