[RESOLVED] Query Help Needed

SirEvenhand

I have a query that is selecting the last entries for each of the last 15 users. the problem is that the current query takes 13.6551 seconds to return the results and this is with a small number of users. I can only imagine the time it will take once I have larger numbers of entries.

Can anyone help me to make the query work more efficiently.


SELECT t1.Ach_ID, t1.Ach_UserName, t1.Ach_TypeID, t1.Ach_Time, t1.Ach_Hash, t1.Ach_Expire, t2.AT_Type, t2.AT_Detail, t2.AT_Image, t2.AT_Detail_2, t2.AT_Detail_3
FROM csv6_network_achievement AS t1, csv6_network_award_types AS t2
WHERE (
t1.Ach_ID, t1.Ach_UserID
)
IN (

SELECT max( Ach_ID ) , Ach_UserID
FROM csv6_network_achievement
GROUP BY Ach_UserID
)
AND t1.Ach_TypeID = t2.AT_ID
ORDER BY t1.Ach_Time DESC
LIMIT 0 , 15

_uk_stuff

Have you tried running an explain on the SQL statement. That will highlight the current fields that the database is using to form the output. You should then be able to identify columns that additional indexes would help speed up the execution time.

Similarly it might need an additonal field to be applied to the database tables such as User ID and Last Access time, in which an index on these fields again would improve performance.

Reference Material: http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

wilzy1

It all depends on what you mean by 'last 15 users'. If you're referring to the last 15 users who signed in, why not have a table for 'Access' and include the user_id, timestamp etc... and run an SQL query on it limiting it to the 15 most recent. Then, run a query against each row returned requesting the respective 'recent post'.

Again... it's all dependant on what you mean.

SirEvenhand

Thanks for the feedback wilzy1 and [uk]stuff

What I have found is that if I make 2 separate queries and use a loop on the first. I can build an array that only populates the unique activity ids and then the second query pulls the information of the joined tables.

QUERY 1 Be sure to use a counter that you can use to break out of the record set return.

$listfifteen = 0;

SELECT Ach_ID, Ach_UserID FROM achievement ORDER BY Ach_Time DESC

while(($GetAchievementID = mysql_fetch_row($rsGetAchievementID)) && $listfifteen < 15)
	{
	if (in_array("$GetAchievementID[1]", $nextqueryarray))
		{
		// do nothing as this member id is already in the array
		}
	else
		{
		if ($listfifteen == 0)
			{
			$sqlquerylist = "t1.Ach_ID = '$GetAchievementID[0]'";
			}
		else
			{
			$sqlquerylist = $sqlquerylist . " OR t1.Ach_ID = '$GetAchievementID[0]'";
			}

	// add to $nextqueryarray
	$nextqueryarray[$listfifteen] = $GetAchievementID[1];

	$listfifteen = $listfifteen + 1;
	}
}

Now that I have the array populated I can pull the second query and it will return the results in the order that I want.

SELECT t1.Ach_ID, t1.Ach_UserName, t1.Ach_TypeID, t2.AT_Type, t2.AT_Detail, t2.AT_Image, t2.AT_Detail_2, t2.AT_Detail_3, t1.Ach_Time, t1.Ach_Hash, t1.Ach_Expire FROM achievement AS t1, award_types AS t2 WHERE t1.Ach_TypeID = t2.AT_ID AND ($sqlquerylist) ORDER BY t1.Ach_Time DESC LIMIT 0 , 15

By doing this I have reduced the drag on the database and sped the query up thousands of percent.

I hope this information can be of assistance to others who face the same issues

Thanks PHP Builder for providing us a place to work together.

wilzy1

Limit the results in your SQL query (LIMIT 15) and remove the uneccessary condition in your while loop.

Weedpacket

I think one query might be sufficient.

Select [i]interestingstuff[/i]
From [i]sometables[/i]
Where t1.Ach_ID in (Select Distinct Ach_ID
                    From achievement
                    Order by Ach_Time Desc Limit 15)

SirEvenhand

Limit the results in your SQL query (LIMIT 15) and remove the uneccessary condition in your while loop.

wilzy1 If you noticed in the first query the id and user are both needed but there may be multiple records for each user so limiting the first query to 15 would only return the last 15 records period.

I think one query might be sufficient.

Select interestingstuff
From sometables
Where t1.Ach_ID in (Select Distinct Ach_ID
                    From achievement
                    Order by Ach_Time Desc Limit 15)

weedpack thanks for the assistance, however acording to MySQL.com DISTINCT works much like GROUP BY, therefor you will have a hard time using ORDER BY in the same query.

As you can see for the original post I was using a sub select but the time it took for the queries to run with a small table (less than 2000 records) was already taking 13.000 seconds per request.

So for others who are finding it hard to come up with a good solution to grouping records and ordering them for the required results I have posted the 2 queries with the looping and array building that has reduced my returns from the 13.000 seconds to 0.0005 seconds which works so much better for my program.

I do thank you both for your assistance and response to the post. It is because of people like yourselves that this forum has come to be such a great resource to myself and many others.

Weedpacket

Oh, yes - the first of the first.... got ahead of myself there

... in (Select max(Ach_ID)
                    From achievement
                    Group by Ach_time
                    Order by Ach_Time Desc Limit 15)

Obviously I don't have your db; but an analogous query on a couple of tables I have handy (outer table 12,000 records, inner table 60,350 records) runs in 47ms.