QuestionsBUG with SQL Query in Ultimate Points Extension - 1.1.8

Questions :question: place them in this section
Previous topicNext topic
User avatar

Topic Author
picaron
Users
Users
    Windows 7 Firefox
Magic user status: Offline
Posts: 7
Joined: Jul 19th, '16, 13:27

BUG with SQL Query in Ultimate Points Extension - 1.1.8

Post by picaron »

Hi all.
Sorry, I do not know if this is the right place to post my comment.

The following SQL query is desproportionate.

Code: Select all

			// Create richest users - cash and bank
			$limit = $points_values['number_show_top_points'];
			$sql_array = array(
				'SELECT'	=> 'u.user_id, u.username, u.user_colour, u.user_points, u.user_avatar, u.user_avatar_type, u.user_avatar_height, u.user_avatar_width, b.holding',

				'FROM'		=> array(
					USERS_TABLE	=> 'u',
				),
				'LEFT_JOIN' => array(
					array(
						'FROM'	=> array($this->points_bank_table => 'b'),
						'ON'	=> 'u.user_id = b.user_id'
					)
				),        
			);
			$sql = $this->db->sql_build_query('SELECT', $sql_array);
My forum has over 28000 users and this causes a delay of more than 22 seconds in ending the query.
I am in localhost.
Image

I have solved it like this.

Code: Select all

			// Create richest users - cash and bank
			$limit = $points_values['number_show_top_points'];
			$sql_array = array(
				'SELECT'	=> 'u.user_id, u.username, u.user_colour, u.user_points, u.user_avatar, u.user_avatar_type, u.user_avatar_height, u.user_avatar_width, b.holding',

				'FROM'		=> array(
					USERS_TABLE	=> 'u',
				),
				'LEFT_JOIN' => array(
					array(
						'FROM'	=> array($this->points_bank_table => 'b'),
						'ON'	=> 'u.user_id = b.user_id'
					)
				),
                // MOD by picaron - start
                'WHERE'		=> 'b.holding > 0',
                'ORDER_BY'	=> 'b.holding DESC, u.username ASC',   
                // MOD by picaron - end                
			);
			$sql = $this->db->sql_build_query('SELECT', $sql_array);
The SQL query is now correct.

Image

Greetings.

This topic has 2 replies

You must be a registered member and logged in to view the replies in this topic.


Register Logout
 
Previous topicNext topic