Group,
First of all thanks to anyone who can respond to this - I am really stumped.
I have been trying to figure this one out and maybe someone out there with a
little deep understanding of joins in sql can give me a hand.
I am working on a system that creates these sql statements on the fly and so
the table names and fields are really perl variables.
This the sql without the join:
select
cs_fld_cs_tbl_l.cs_type,field_name,name,type,type_sql,rl_table,cs_tbl from
cs_fld, cs_fld_cs_tbl_l
where cs_fld_cs_tbl_l.cs_fld_id = cs_fld.cs_rid
and cs_fld_cs_tbl_l.cs_tbl_id = '23' AND cs_fld_cs_tbl_l.cs_type =
'basic'
+---------+------------+------------------+------------+-------------+------
----+--------+
| cs_type | field_name | name | type | type_sql |
rl_table | cs_tbl |
+---------+------------+------------------+------------+-------------+------
----+--------+
| basic | status | Status | recordid | int |
status | [23] |
| basic | body | Main Body | textarea | text |
| [23] |
| basic | section | Section | recordid | int |
demsect | [23] |
| basic | title | Title | text | varchar(50) |
| [23] |
| basic | assignu | Assign to User: | recordid | int | u
| [23] |
| basic | subsect | Subsection | recordlist | list |
subsect | [23] |
| basic | assigngr | Assign to Group: | recordid | int |
cs_com | [23] |
| basic | sorder | Relative Order | numeric | int |
| [23] |
| basic | con_type | Content Type: | recordid | int |
con_type | [23] |
+---------+------------+------------------+------------+-------------+------
----+--------+
9 rows in set (0.01 sec)
This is the join:
select
cs_fld_cs_tbl_l.cs_type,field_name,name,type,type_sql,rl_table,cs_tbl from
cs_fld left join cs_fld_cs_tbl_l
ON cs_fld_cs_tbl_l.cs_fld_id = cs_fld.cs_rid
WHERE cs_fld_cs_tbl_l.cs_tbl_id = '23' AND cs_fld_cs_tbl_l.cs_type =
'basic'
+---------+------------+------------------+------------+-------------+------
----+--------+
| cs_type | field_name | name | type | type_sql |
rl_table | cs_tbl |
+---------+------------+------------------+------------+-------------+------
----+--------+
| basic | status | Status | recordid | int |
status | [23] |
| basic | body | Main Body | textarea | text |
| [23] |
| basic | title | Title | text | varchar(50) |
| [23] |
| basic | subsect | Subsection | recordlist | list |
subsect | [23] |
| basic | assigngr | Assign to Group: | recordid | int |
cs_com | [23] |
| basic | section | Section | recordid | int |
demsect | [23] |
| basic | sorder | Relative Order | numeric | int |
| [23] |
| basic | assignu | Assign to User: | recordid | int | u
| [23] |
| basic | con_type | Content Type: | recordid | int |
con_type | [23] |
+---------+------------+------------------+------------+-------------+------
----+--------+
9 rows in set (4.44 sec)
Notice the time difference? I thought that the join statement was supposed
to be more efficient.
I did some dinking with the joins and I go this:
It appears that optimization of joins depends upon what table you are
joining to what:
This is the new sql:
mysql> select
-> cs_fld_cs_tbl_l.cs_type,field_name,name,type,type_sql,rl_table,cs_tbl
from
-> cs_fld_cs_tbl_l left join cs_fld
-> ON cs_fld_cs_tbl_l.cs_fld_id = cs_fld.cs_rid
-> WHERE cs_fld_cs_tbl_l.cs_tbl_id = '23' AND cs_fld_cs_tbl_l.cs_type
= 'basic';
+---------+------------+------------------+------------+-------------+------
----+--------+
| cs_type | field_name | name | type | type_sql |
rl_table | cs_tbl |
+---------+------------+------------------+------------+-------------+------
----+--------+
| basic | status | Status | recordid | int |
status | [23] |
| basic | body | Main Body | textarea | text |
| [23] |
| basic | section | Section | recordid | int |
demsect | [23] |
| basic | title | Title | text | varchar(50) |
| [23] |
| basic | assignu | Assign to User: | recordid | int | u
| [23] |
| basic | subsect | Subsection | recordlist | list |
subsect | [23] |
| basic | assigngr | Assign to Group: | recordid | int |
cs_com | [23] |
| basic | sorder | Relative Order | numeric | int |
| [23] |
| basic | con_type | Content Type: | recordid | int |
con_type | [23] |
+---------+------------+------------------+------------+-------------+------
----+--------+
9 rows in set (0.01 sec)
All I did was transpose the cs_fld table with the cs_fld_cs_tbl_l and bam! I
get the more efficient time.
My question is: How do I determine which table should be on which side of
the join statement?
Thanks to all who respond - I really am stumped on this one.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
