Advanced Coding Issue (Speed Related)

[mcmuney]

[b]Overview[/b]: I'm trying to figure out what's causing a specific function to be EXTREMELY SLOW. I have a beta online community site with approximately 800 members. On the browse page, there's the option to browse only users with photos, which is the default OR to browse for all users, by checking "Include users without photos" checkbox.
[b]Additional Info[/b]: Of the 800 members, roughly 400 have photos.

[b]PROBLEM[/b]: The default search (photos only - 400 results) is fairly fast, averages < .2 secs on DSL/Cable. However, when searched to include all results (about 800 results), twice as the default search, it averages about 13 secs on same connection. At the very max, it should take double the time < .4 secs.

[b]View Full Code[/b]: [url=http://rateometer.com/test.txt]http://rateometer.com/test.txt[/url]
[b]Test Problem[/b]: [url=http://rateometer.com/social_browse_users.php]http://rateometer.com/social_browse_users.php[/url] (to test, just click on this link and click the Search button, the search time is displayed at the bottom/left of the page. Now, check the "Include users with photos" checkbox and attempt the same search and note the time difference)

Any help on identifying the root of the problem will be greatly appreciated.

[Travis Estill]

Are the related SQL image fields indexed?

Travis

[mcmuney]

Yes, all images are cropped and indexed into smaller sized thumbs. But this shouldn't be the issue, because the search results are fast by default (with member photos). When it includes members WITHOUT photos, the search time drags, this should only add 2 no photo images (one for each gender).

[Travis Estill]

Well, I was referring to your database query. Specifically these lines:

[code]if($Photos == "")
  {
  $search_q= $search_q." and m_img.sci_main=1 and m.scm_mem_id = m_img.scm_mem_id";
  $search_q_num.=" and m_img.sci_main=1 and m.scm_mem_id = m_img.scm_mem_id";
  }[/code]

Can you explain what's going on here? It looks like extra restrictions are being placed on the query if the user includes members [i]without[/i] photos in their search. But it seems like only the opposite would make sense.

[.josh]

a) you should not use the global variables for posted items that are a product of register_globals being set to ON. 

b) you should not use the GET method in your form, although i see you've made some kind of effort in security by using a token..

c) you have made no effort to sanitize any of your variables, just inserting them directly into your query, far as i can tell, unless your class method that you send your query to does it

d) i see this:
[code]
if($Photos == "")
{
$search_q= $search_q." and m_img.sci_main=1 and m.scm_mem_id = m_img.scm_mem_id";
$search_q_num.=" and m_img.sci_main=1 and m.scm_mem_id = m_img.scm_mem_id";
}
[/code]
maybe i'm blind but I don't see an alternative for if there IS a value...

e) you have this big block of if's that should be condensed to a switch statement

f) can you please echo out and post both search query examples