[SOLVED] Help with a very long query

[solinent]

Well I have a query that looks like this, that runs WITHIN another query

SELECT username, time FROM gthd WHERE car='$car' ORDER BY time

 

now, that is then looked through to find the user's "rank":

$db2->query("SELECT username, time FROM gthd WHERE car='$car' ORDER BY time");
		$n=0;
		while ($db2->sql_array()) {
			$n++;
			if ($db2->recordSet[0]==$usr && $db2->recordSet[1]==$db->recordSet[0]) {
				break;
			}
		}
		$rank=$n;

 

Now, this takes very long.  For a very big user, it took 34.4283781052 seconds.

 

Now, I would love SQL even more if there was a built in way to do what I'm doing (selecting all the records, then finding at record it is).

 

Basically I need to run a SQL query on top of a SQL query, and then retrieve where within that first query the second one exists.

 

For example

 

SELECT name FROM names WHERE cat=1 ORDER BY score

SELECT name WHERE name='Bob'

 

Now, I need to find where the second record set exists within the first record set ($db2->query is simply a wrapper to make it easier on me to use multiple connections to the database).

[fenway]

Huh? What do you actually want to do?

[solinent]

Yeah it's very confusing to try to explain.

 

Basically, really abstract:

 

Imagine a high-scores list.  Now, we have a username, and we want to figure out what place they are in the high scores list.  But I don't want to have to loop through every single one, because there are potentially 5000 positions (which would take quite a long time to load a page, and huge server load or w/e) Actually, I tried that, and it took 18 seconds to load someone with alot of times on the list.

 

Is there a way using SQL I can retrieve their position on the list?

[lightningstrike]

maybe you should look into the LIMIT clause to increase the speed, and show only x results per page.

[solinent]

maybe you should look into the LIMIT clause to increase the speed, and show only x results per page.

 

I can't though, if I hardcode a LIMIT, then people who are beyond that limit will be gone

 

Any other ideas?

 

EDIT: username is not a unique field.  I do have a unique ID field if there's anything I can do with that?

[solinent]

I think I have an idea, but it doesn't work perfectly.

 

Well all I do is loop through every single category and assign a static rank.

 

When I update all the records once a week, I'll simply re-run the ranking script.

 

This way it improved a 30 sec load time to  a .5 sec

 

 

[fenway]

Yup, summary tables are just for that.  But I'm confused... how are there being ranked?

[solinent]

Simply a time.

 

See GT:HD on the internet if you're really interested.

 

My website which is now running fine is at http://runeap.com/gthd/ and it should show you what I mean.  Enter "solinent' or just click "leaderboards".