| Author |
Topic |
|
ack7634
Starting Member
4 Posts |
 Posted - 2008-08-29 : 11:09:42
|
| I am working on a php tracker site for my job and I am not too much of a database guy and I guess I have a bad design because just returning two records or more from this table is pretty slow takes about 20-30 seconds. My select statement involves quite a few joins: $sql_statement = "SELECT cb.ID, celltype.ctype, months.month, telephia.t_t, " . "cb.site_name, cb.towner, re.re_t, cm.cm_t, " . "market.mrkt, cb.fac, cb.rtype, cb.stype, emis_info.emis_info_t, bpntp.bpntp_t, " . "req1.reqorrec, cb.reg_con_appr_d, exp1.expbyorx, cb.bldgset_d, exp2.expbyorx, " . "cb.ac_pwr_avail_d, towerlight.tl_t, exp3.expbyorx, cb.ant_install_d, req2.reqorrec, cb.equip_d, " . "osix1.osix, cb.pwr_plnt_d, osix2.osix, cb.luc_d, exp4.expbyorx, cb.telco_ready_d, " . "copfib.cf_t, tuppkg.pkg_t, cb.tuppkg_d, reginsvc.reginsvc_t, cb.reg_in_svc_d, cb.site_accpt_d, " . "complete.com_t, cb.complete_d, cb.cm_comments, cb.fac_comments, " . "cb.t1_due, cb.supps_issd, cb.req_wkfl, cb.cons_mgr_req_dd, cb.sizing, cb.meet_pt, cb.lec_name, cb.t1_accpt, " . "cb.micro_equip, cb.micro_end, cb.micro_install " . "FROM cellbuild cb, celltype, months, telephia, " . "re, cm, market, emis_info, bpntp, " . "reqorrec req1, expbyorx exp1, expbyorx exp2, " . "towerlight, expbyorx exp3, reqorrec req2, " . "osix osix1, osix osix2, expbyorx exp4, " . "copfib, tuppkg, reginsvc, " . "complete " . "WHERE cb.ctype_id = celltype.ID AND cb.month_id = months.ID AND cb.telephia_id = telephia.ID " . "AND cb.re_id = re.ID AND cb.cm_id = cm.ID AND cb.mrkt_id = market.ID AND cb.emis_info_id = emis_info.ID AND cb.bpntp_id = bpntp.ID " . "AND cb.reg_con_appr_id = req1.ID AND cb.bldgset_id = exp1.ID AND cb.ac_pwr_avail_id = exp2.ID " . "AND cb.towerlight_id = towerlight.ID AND cb.ant_install_id = exp3.ID AND cb.equip_id = req2.ID " . "AND cb.pwr_plnt_id = osix1.ID AND cb.luc_id = osix2.ID AND cb.telco_ready_id = exp4.ID " . "AND cb.copfib_id = copfib.ID AND cb.tuppkg_id = tuppkg.ID AND cb.reg_in_svc_id = reginsvc.ID " . "AND cb.complete_id = complete.ID"; |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-08-29 : 12:47:00
|
| so whats your question? |
 |
|
|
ack7634
Starting Member
4 Posts |
Posted - 2008-08-29 : 13:14:26
|
| What can I do to speed up the select query?My bad i pressed post too early. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-29 : 13:21:35
|
| Do you have proper indexes? Check Execution plan. |
|
|
|
ack7634
Starting Member
4 Posts |
Posted - 2008-08-29 : 14:40:53
|
| I hope this doesn't sound too dumb, but how do I check for proper indexes? Also I think my problem from reading out and about is that I link too many small tables toegther when isntead of joining the data, I might as well as store that particular data within the cellbuild table itself. Basicly instead of doing a join, just store that particular data in the table. |
|
|
|
ack7634
Starting Member
4 Posts |
Posted - 2008-08-29 : 15:20:41
|
| here is an explain ran on my select, whats the Using where and index from those two joins?+------------+--------+---------------+---------+---------+--------------------+------+-------------+| table | type | possible_keys | key | key_len | ref | rows | Extra |+------------+--------+---------------+---------+---------+--------------------+------+-------------+| cb | ALL | NULL | NULL | NULL | NULL | 2 | || celltype | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.ctype_id | 1 | || months | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.month_id | 1 | || re | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.re_id | 1 | || cm | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.cm_id | 1 | || market | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.mrkt_id | 1 | || emis_info | ALL | PRIMARY,id | NULL | NULL | NULL | 2 | Using where || telephia | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.telephia_id | 1 | || bpntp | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.bpntp_id | 1 | || req1 | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.reg_con_appr_id | 1 | || exp1 | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.bldgset_id | 1 | || exp2 | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.ac_pwr_avail_id | 1 | || towerlight | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.towerlight_id | 1 | || exp3 | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.ant_install_id | 1 | || req2 | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.equip_id | 1 | || osix1 | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.pwr_plnt_id | 1 | || osix2 | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.luc_id | 1 | || exp4 | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.telco_ready_id | 1 | || copfib | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.copfib_id | 1 | || tuppkg | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.tuppkg_id | 1 | || reginsvc | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.reg_in_svc_id | 1 | || complete | eq_ref | PRIMARY,id | PRIMARY | 4 | cb.complete_id | 1 | Using index |+------------+--------+---------------+---------+---------+--------------------+------+-------------+ |
|
|
|
|
|
|
Slow SQL Select Statement Query