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?
