search engine help SLOW

[nadeemshafi9]

hello guys i have a large database of text, i use php and singular SQL statments to find key words entered in the search by the user in a record then i find the key words in any recocrd relatred to the first one in RDB style.

 

i have also tried breaking the M:M relations that where tehre before i came and then adding XREF tables and using a SINGLE SQL statement in the WHERE CLAUSE.

 

the PHP with multiple SQL is faster than the single SQL statment by 1 second why ? my search takes 8 seconds, it involves 7 tables and 5 XREF tables to acommedate for the M:M relations.

 

note i didnt use JOIN i used the = in the WHERE CLAUSE to join the tables ???

 

 

any help any sugestions thanks. 

 

$this_rds = new rds_class();
$search_rds_rs = $this_rds->get_all();


for($j=0; $j < mysql_num_rows($search_rds_rs); $j++) {
	$sql = "SELECT p.physreq_title, p.physreq_text, e.enviroserv_title, e.enviroserv_text, f.finish_title, f.finish_text ". 
	"FROM ".
	"physreq_table p, ".
	"enviroserv_table e, ".
	"finish_table f, ".
	"xref_physreq_rds x_p_r, ".
	"xref_enviroserv_rds x_e_r, ".
	"xref_finish_rds x_f_r, ".
	"rds_table r ".
	"WHERE ".
	"p.physreq_id = x_p_r.physreq_id ".
	"AND r.rds_id = x_p_r.rds_id ".
	"AND e.enviroserv_id = x_e_r.enviroserv_id ".
	"AND r.rds_id = x_e_r.rds_id ".
	"AND f.finish_id = x_f_r.finish_id ".
	"AND r.rds_id = x_f_r.rds_id ".
	"AND r.rds_id = '".$this_rds->rds_id."';";
}

[jaymc]

How many rows are there in your tables roughly

 

To be honest its always going to take a while.. but Im sure it can be cut down a touch frmo 7 seconds

[nadeemshafi9]

about 25 feilds per table about 30 tables, but i have 3 more XREF tables made to patch the many to many join that was made prior  to my arival, mysql 4 on testing server and mysql 5 on live server, but php 4 on both

[nadeemshafi9]

cmon guys help

[BlueSkyIS]

What data types are the pertinent keyword search columns? Are they set up for FULL TEXT searches?

[nadeemshafi9]

What data types are the pertinent keyword search columns? Are they set up for FULL TEXT searches?

 

how do u set them up for full text searches, yes they are text types plz elabourate ????

[sayedsohail]

Dear nadeem,

 

You definately can improve your search if you replace where = with inner join, infact i was in the same boat as you in the past and inner joins had made a big difference for linking more than 5 tables here is the part of my select statement an example of how i used it:  The real one has got twenty joins.

 

select .....

from service_summary ss

inner

  join sview shv

    on shv.t_id = ss.t_id 

  and shv.s_date = ss.s_date

inner

  join checklist_items ci

    on ci.id = shv.service_id

order

    by ss.t_id

    , ss.s_date;