database tables and query problems

OwenTheSamoan

I'm using php with mysql with my website. I have about ten thousand members and there is a search option available to all the members. The prooblem I am having is when the members do a search that the tables sometimes lock up and the site almost comes to a grinding halt for a minute or two. I'm using this command to view my database:

show processlist;

under the STATE column I'm getting:
"Copying to tmp table"

The queries underneath this are:
"Locked"

I've added more memory, tried like hell to make the query script as efficient as possible. Any suggestions?

Thanks,
Owen The Samoan

Frederick

post code so we can see

OwenTheSamoan

Here's an abbreviated version of the code. The actual code is a lot longer. THANKS!

$query = "SELECT distinct a.username, a.sex, a.state, a.age, a.height, a.ethnicity, a.haircolor, a.last_login FROM profile as a, images as b WHERE";

$query .= " (a.sex in ('$sex'))";

if($state != All)
$query .= " AND (a.state in ('$state'))";

$query .= " AND (a.age between '$age1' and '$age2')";

$query .= " AND(a.height between '$height1' and '$height2')";

if(!empty($ethnicity1) or !empty($ethnicity2) or !empty($ethnicity3) or !empty($ethnicity4) or !empty($ethnicity5) or !empty($ethnicity6) or !empty($ethnicity7) or !empty($ethnicity8))
$query .= " AND (a.ethnicity in ('$ethnicity1 ','$ethnicity2 ','$ethnicity3 ','$ethnicity4 ','$ethnicity5 ','$ethnicity6 ','$ethnicity7 ','$ethnicity8','$ethnicity9'))";

if(!empty($haircolor1) or !empty($haircolor2) or !empty($haircolor3) or !empty($haircolor4) or !empty($haircolor5) or !empty($haircolor6) or !empty($haircolor7))
$query .= " AND (a.haircolor in ('$haircolor1 ','$haircolor2 ','$haircolor3 ','$haircolor4 ','$haircolor5 ','$haircolor6 ','$haircolor7 '))";

if(!empty($photo))
$query .=" AND a.username = b.username AND ( b.img_id != 0)";

$query .= " order by last_login desc";

tomhath

It looks to me that if $photo is not empty, your query will join every row in images with every row in profile. That's probably a very large number of rows.

I can't tell from the sql you posted what you are trying to do here (you never retrieve any columns from images, what's it there for?)

I'm going to guess that what you really need is a LEFT JOIN

SELECT distinct ... 
FROM profile as a
LEFT JOIN  images as b
ON a.username = b.username
WHERE <everything else>
...

OwenTheSamoan

Thanks for your help on this. The reason for the second table (images) is so that if someone wants to do a search of the members on my site, they can choose whether or not they can select from members that have photos only. I'll try what you suggested. The second table (images) is in the script only to check and compare which members in the query have photos of themselves or not.