Code improvement in session.php (phpbb 2.2)

Discuss features as they are added to the new version. Give us your feedback. Don't post bug reports, feature requests, support questions or suggestions here.
Forum rules
Discuss features as they are added to the new version. Give us your feedback. Don't post bug reports, feature requests, support questions or suggestions here. Feature requests are closed.
Post Reply
jasonlambert
Registered User
Posts: 1
Joined: Tue Oct 12, 2004 8:48 pm

Code improvement in session.php (phpbb 2.2)

Post by jasonlambert »

Hiya,

I downloaded the code for the new phpbb 2.2 the other day to have a browse through and experiment. I found 1 query in includes/session.php in the create() function that was creating a temporary table - the following "mod" splits that query into 2 queries.

First of all, the query in question is:

Code: Select all

// Grab user data ... join on session if it exists for session time
                $sql = 'SELECT u.*, s.session_time, s.session_id
                        FROM ' . USERS_TABLE . ' u
                        LEFT JOIN ' . SESSIONS_TABLE . " s ON s.session_user_id = u.user_id
                        WHERE u.user_id = $user_id
                        ORDER BY s.session_time DESC";
Does this:

Code: Select all

Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
u	const	PRIMARY	PRIMARY	3	const	1	Using temporary; Using filesort
s	ref	session_user_id	session_user_id	3	const	19	 
so, to fix, do this:



FIND:

Code: Select all

                // Grab user data ... join on session if it exists for session time
                $sql = 'SELECT u.*, s.session_time, s.session_id, g.*
                        FROM (' . USERS_TABLE . ' u, ' . GROUPS_TABLE . ' g
                        LEFT JOIN ' . SESSIONS_TABLE . " s ON s.session_user_id = u.user_id)
                        WHERE u.user_id = $user_id
                                AND u.group_id = g.group_id
                        ORDER BY s.session_time DESC";
                $result = $db->sql_query_limit($sql, 1);

                $this->data = $db->sql_fetchrow($result);
                $db->sql_freeresult($result);

REPLACE WITH:

Code: Select all

                $sql = "SELECT s.session_time, s.session_id FROM ". SESSIONS_TABLE ." s WHERE s.session_user_id = {$user_id} ORDER BY s.session_time DESC";

                $result = $db->sql_query_limit($sql, 1);

                $this->data = $db->sql_fetchrow($result);
                $db->sql_freeresult($result);


                $sql = 'SELECT * FROM ' . USERS_TABLE . ' WHERE user_id = ' . $user_id;
                $result = $db->sql_query($sql);

                $tmp = $db->sql_fetchrow($result);

                foreach( $tmp as $datakey => $dataval )
                        $this->data[$datakey] = $dataval;

                $db->sql_freeresult($result);
This will now give you:
Query 1:

Code: Select all

Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
s	range	session_user_id	session_user_id	3	 	445	Using where; Using filesort
Query 2:

Code: Select all

Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
users	const	PRIMARY	PRIMARY	3	const	1	 

Creating an index on phpbb_sessions.session_user_id should be considered to improve the performance of (the new) query #1 a bit more.

Just a suggestion.

SamG
Registered User
Posts: 1241
Joined: Fri Aug 31, 2001 6:35 pm

Re: Code improvement in session.php (phpbb 2.2)

Post by SamG »

Hi,

These types of suggestions will be most useful after the software is complete and released at the beta stage. Thanks.
"I hate trolls!" - Willow Ufgood

BartVB
Trapped inside rank factory, send help!
Posts: 335
Joined: Thu Aug 02, 2001 1:32 pm
Location: The Netherlands
Contact:

Re: Code improvement in session.php (phpbb 2.2)

Post by BartVB »

BTW it would be even more efficient to switch the order of the two queries. Only two fields are fetched from the sessions table, the users table is quite a bit bigger which would save on the foreach loop.
I Hate oversized sigs and Love Penguins :D

User avatar
Viperal2
Registered User
Posts: 223
Joined: Tue Jun 08, 2004 9:28 pm
Contact:

Re: Code improvement in session.php (phpbb 2.2)

Post by Viperal2 »

Considering you can combine the arrays += ( not .= ) ( tested in php5 ) there isn't a need to do a loop.

Also you left out the groups table in you code ...

It is kind of odd i don't get the temp table, maybe it my mysql (my my lol) version.
With from the main startup function, maye it the way it layed out in the create. Don't have time to test and i can't post my result or recommendation until the beta release :( ..

So will just say these ;)

Post Reply