Any ideas on this mysql query?

[noj75]

Hi to all on this great site! I am desperate for some help:


$samesurn = "SELECT * FROM $stb WHERE s_sur IN (SELECT s_sur FROM $stb WHERE s_name='$proname') AND s_name!='$proname' ";


I am trying to do the above query:

$proname has a list of values in s_sur. I want to query the database to see who else has the same values and then display the results excluding $proname's own results.

This does work but is TERRIBLY slow. Is there another way to do this?

Any help is greatly appreciated. Thank you.

[Marco van Herwaarden]

Maybe i misread it, but on first sight this doesn'tmake sense at all.

In the sub-query you select all surnames (assumption) that have a first name $proname. With the resulting surnames, you select again from the $stb table, and want to list all with the selected surnames, but who don't have a first name of $proname. That should result in an empty resultset.

[noj75]

Hi Marco,

$proname (s_name) is the username of the person who entered the s_sur (surname) records.

I want to query the db to see who else has entered the same (surnames) s_sur as $proname and return the results. But I want all surnames from $proname exluded.

Does that make more sense?

[Marco van Herwaarden]

Sorry but no.

You want to list all surnames that have $proname, but exclude those that have $proname.

[noj75]

LOL,

Basically, I (I am $proname, s_name) have entered numerous surnames into the database (s_sur). I want to see if any other users have entered the same surnames as I have and have the database show the rusults to me but I dont want to see my own entries, only other members entries.

So, I have to do a query to get ALL the surnames I have entered and then put that into another query to see who else has the same surnames.

How about that?

The query DOES work, it's just slow.

[Marco van Herwaarden]

Are there any indexes on these columns?

[noj75]

Yes s_sur and s_name

This has speeded it up alot. Could it be faster?

[Guest190829]

The subquery is probably causing the slow load times and is probably not even necessary if your table is normalized enough.

If you have an id associated with the user who submitted the data, you can just do a simple select using the s_name and s_sur of the user with a conditional to exclude records submitted by that user id.