PHP Search query performance

[aldrin151]

Hi everyone...just had a question...I'm using a search form which has around 70 params, for example

 

$sWhere = "WHERE (tut=" . tosql(get_param("tut"), "Number") . " OR alg_tut=" . tosql(get_param("alg_tut"), "Number") . " OR cal_tut=" . tosql(get_param("cal_tut"), "Number") . " OR geo_tut=" . tosql(get_param("geo_tut"), "Number") . " OR bio_tut=" . tosql(get_param("bio_tut"), "Number") . " OR tri_tut=" . tosql(get_param("tri_tut"), "Number") . " OR che_tut=" . tosql(get_param("che_tut"), "Number") . " OR ph_tut=" . tosql(get_param("ph_tut"), "Number") . " OR ear_tut=" . tosql(get_param("ear_tut"), "Number") . " OR ess_tut=" . tosql(get_param("ess_tut"), "Number") . " OR pro_tut=" . tosql(get_param("pro_tut"), "Number") . " OR li_tut=" . tosql(get_param("li_tut"), "Number") . " OR res_tut=" . tosql(get_param("res_tut"), "Number") . " OR ush_tut=" . tosql(get_param("ush_tut"), "Number") . " OR worl_tut=" . tosql(get_param("worl_tut"), "Number") . ") AND (alb_lang=" . tosql(get_param("alb_lang"), "Number") . " OR ara_lang=" . tosql(get_param("ara_lang"), "Number") . " OR arm_lang=" . tosql(get_param("arm_lang"), "Number") . " OR chi_lang=" . tosql(get_param("chi_lang"), "Number") . " OR cz_lang=" . tosql(get_param("cz_lang"), "Number") . " OR du_lang=" . tosql(get_param("du_lang"), "Number") . " OR fa_lang=" . tosql(get_param("fa_lang"), "Number") . " OR fre_lang=" . tosql(get_param("fre_lang"), "Number") . " OR ger_lang=" . tosql(get_param("ger_lang"), "Number") . " OR gree_lang=" . tosql(get_param("gree_lang"), "Number") . " OR heb_lang=" . tosql(get_param("heb_lang"), "Number") . " OR hin_lang=" . tosql(get_param("hin_lang"), "Number") . " OR ital_lang=" . tosql(get_param("ital_lang"), "Number") . " OR japa_lang=" . tosql(get_param("japa_lang"), "Number") . " OR kore_lang=" . tosql(get_param("kore_lang"), "Number") . " OR portu_lang=" . tosql(get_param("portu_lang"), "Number") . " OR pun_lang=" . tosql(get_param("pun_lang"), "Number") . " OR roma_lang=" . tosql(get_param("roma_lang"), "Number") . " OR russ_lang=" . tosql(get_param("russ_lang"), "Number") . " OR spa_lang=" . tosql(get_param("spa_lang"), "Number") . " OR tag_lang=" . tosql(get_param("tag_lang"), "Number") . " OR tu_lang=" . tosql(get_param("tu_lang"), "Number") . " OR uk_lang=" . tosql(get_param("uk_lang"), "Number") . " OR other_lang=" . tosql(get_param("other_lang"), "Number") . ") AND essa ". "like " . tosql("%".get_param("essay") ."%", "Text") .  "";

 

 

My concern is, does this effect performance and is this a correct or normal way of doing this?

 

Thanks...

 

[discomatt]

Yes, this WILL affect performance. I'm not sure what you're trying to do with this mess, but I suggest narrowing down the parameters with some user input.

[aldrin151]

lol, that's what I thought...I'm thinking how to construct the search page depending on what the user inputs then narrowing down the query, like you suggested...thanks for your input

[discomatt]

Well, keep in mind my suggestion is purely based on efficiency. If your site doesn't get a huge amount of hits, or your database is only a few hundred entries, it probably wouldn't hurt that bad.

[aldrin151]

Need to really keep efficiency in mind...I'm going to go ahead and break it down for sure...I would like to have alot of users one day

[aldrin151]

Just one more question...what if I modify my search to using a drop down list for the categories being search, rather than checkboxes where the user selects multiple categories...would that make a difference in performance?...For example

 

my $sSQL reads

 

WHERE (bar_int=1 OR cas_int=NULL OR dj_int=NULL OR dri_int=NULL OR hou_int=NULL OR spo_int=NULL) AND essay like '%%'

 

this is when I select only one category to search where the other categories are submitted as NULL

 

rather than the below

 

WHERE (bar_int=1 OR cas_int=1 OR dj_int=1 OR dri_int=1 OR hou_int=NULL OR spo_int=NULL) AND essay like '%%'

 

 

 

[discomatt]

The more parameters you add to your WHERE clause, the more MySQL has to work