I found a big problem in Mysql... not sure which part i did wrong.
I need to select more 18 table at one time... and it works fine when i have only one row of data... but when i have two rows, i found that it become really slow when i select more than 13/14 table at one time.... here is the sample query... hope someone has the same experience share with me..
SELECT * FROM program, condition, condition_fn, condition_imp, condition_lpurpose, condition_mi, condition_occupancy, condition_pnty, condition_sassets, condition_semployment, condition_sincome, condition_units, condition_vassets, condition_vemployment, condition_ptype, condition_vincome, condition_state, state WHERE program_type='12' AND program_second = '2' AND program_disable = '0' AND condition.program_id = program.program_id AND condition_maxla >= '200000' AND condition_minla <= '200000' AND condition_maxltv >= '80' AND condition_maxcltv >= '80' AND condition_mincs <= '650' AND condition_disable = '0' AND condition_fn.condition_id = condition.condition_id AND condition_fn = '2' AND condition_imp.condition_id = condition.condition_id AND condition_imp = '1' AND condition_lpurpose.condition_id = condition.condition_id AND condition_lpurpose = '1' AND condition_mi.condition_id = condition.condition_id AND condition_mi = '2' AND condition_occupancy.condition_id = condition.condition_id AND condition_occupancy = '1' AND condition_pnty.condition_id = condition.condition_id AND condition_pnty = '0' AND condition_ptype.condition_id = condition.condition_id AND condition_ptype = '1' AND condition_sassets.condition_id = condition.condition_id AND condition_sassets = '1' AND condition_semployment.condition_id = condition.condition_id AND condition_semployment = '1' AND condition_sincome.condition_id = condition.condition_id AND condition_sincome = '1' AND condition_units.condition_id = condition.condition_id AND condition_units = '1' AND condition_vassets.condition_id = condition.condition_id AND condition_vassets = '2' AND condition_vemployment.condition_id = condition.condition_id AND condition_vemployment = '2' AND condition_vincome.condition_id = condition.condition_id AND condition_vincome = '2' ANd condition_state.condition_id = condition.condition_id AND condition_state.state_id = '005' AND condition_state.state_id = state.state_id AND state.state_disable = '0' GROUP BY program.program_id
I need to select more 18 table at one time... and it works fine when i have only one row of data... but when i have two rows, i found that it become really slow when i select more than 13/14 table at one time.... here is the sample query... hope someone has the same experience share with me..
SELECT * FROM program, condition, condition_fn, condition_imp, condition_lpurpose, condition_mi, condition_occupancy, condition_pnty, condition_sassets, condition_semployment, condition_sincome, condition_units, condition_vassets, condition_vemployment, condition_ptype, condition_vincome, condition_state, state WHERE program_type='12' AND program_second = '2' AND program_disable = '0' AND condition.program_id = program.program_id AND condition_maxla >= '200000' AND condition_minla <= '200000' AND condition_maxltv >= '80' AND condition_maxcltv >= '80' AND condition_mincs <= '650' AND condition_disable = '0' AND condition_fn.condition_id = condition.condition_id AND condition_fn = '2' AND condition_imp.condition_id = condition.condition_id AND condition_imp = '1' AND condition_lpurpose.condition_id = condition.condition_id AND condition_lpurpose = '1' AND condition_mi.condition_id = condition.condition_id AND condition_mi = '2' AND condition_occupancy.condition_id = condition.condition_id AND condition_occupancy = '1' AND condition_pnty.condition_id = condition.condition_id AND condition_pnty = '0' AND condition_ptype.condition_id = condition.condition_id AND condition_ptype = '1' AND condition_sassets.condition_id = condition.condition_id AND condition_sassets = '1' AND condition_semployment.condition_id = condition.condition_id AND condition_semployment = '1' AND condition_sincome.condition_id = condition.condition_id AND condition_sincome = '1' AND condition_units.condition_id = condition.condition_id AND condition_units = '1' AND condition_vassets.condition_id = condition.condition_id AND condition_vassets = '2' AND condition_vemployment.condition_id = condition.condition_id AND condition_vemployment = '2' AND condition_vincome.condition_id = condition.condition_id AND condition_vincome = '2' ANd condition_state.condition_id = condition.condition_id AND condition_state.state_id = '005' AND condition_state.state_id = state.state_id AND state.state_disable = '0' GROUP BY program.program_id