Slow MySQL queries w/ WebLoginPE

19 Posts

withincontext Reply #1, 16 years, 7 months ago

I’m using WebLoginPE on a site for a major network t.v. show. We haven’t even started advertising the site yet and we have had about 1,500 users sign-up in the last week. I have an cached document that uses a template w/ an uncached snippet:

<?xml version="1.0" encoding="UTF-8"?>
<root>
	<stats>
		Query time: [^qt^] ||| # of Queries: [^q^] ||| PHP parsing time: [^p^] ||| Total load time: [^t^] ||| Source: [^s^]
	</stats>
	<users>
		[!WebLoginPE? &type=`users` &usersOuterTpl=`User:AllUsersWrapper` &usersTpl=`User:AllUsers` &messageTpl=`User:ServerResponse` &usersList=`Members:default:default:internalKey:ASC:approved(1)`!]
	</users>
</root>

Here is the output:
Query time: 14.2906 s ||| # of Queries: 1542 ||| PHP parsing time: 0.5671 s ||| Total load time: 14.8577 s ||| Source: database

Any idea why the query time is so long? I’m thinking that it might be the QueryDbForUser function (and related queries) the webloginpe.class.php:

	/**
	 * QueryDbForUser
	 * Queries the web_users table for $_REQUEST['username'].
	 *
	 * @param string $Username The username of the user to query for.
	 * @return void
	 * @author Raymond Irving
	 * @author Scotty Delicious
	 */
	function QueryDbForUser($Username)
	{
		global $modx;
		
		$web_users = $modx->getFullTableName('web_users');
		$web_user_attributes = $modx->getFullTableName('web_user_attributes');
		
		$query = "SELECT * FROM ".$web_users.", ".$web_user_attributes.", ".$this->CustomTable." WHERE BINARY LOWER(".$web_users.".username) = '".strtolower($Username)."' AND ".$web_user_attributes.".`internalKey` = ".$web_users.".`id` AND ".$this->CustomTable.".`internalKey` = ".$web_users.".`id`";
		$query2 = "SELECT * FROM ".$web_users.", ".$web_user_attributes.", ".$this->CustomTable." WHERE(".$web_users.".username) = '".$Username."' AND ".$web_user_attributes.".`internalKey` = ".$web_users.".`id` AND ".$this->CustomTable.".`internalKey` = ".$web_users.".`id`";
		if (!$limit = $modx->db->getRecordCount($dataSource = $modx->db->query($query))) $limit = $modx->db->getRecordCount($dataSource = $modx->db->query($query2));
		
		if ($limit == 0)
		{
			$query = "SELECT * FROM ".$web_users.", ".$web_user_attributes." WHERE BINARY LOWER(".$web_users.".username) = '".strtolower($Username)."' AND ".$web_user_attributes.".`internalKey` = ".$web_users.".`id`";
			$query2 = "SELECT * FROM ".$web_users.", ".$web_user_attributes." WHERE(".$web_users.".username) = '".$Username."' AND ".$web_user_attributes.".`internalKey` = ".$web_users.".`id`";
			if (!$limit = $modx->db->getRecordCount($dataSource = $modx->db->query($query))) $limit = $modx->db->getRecordCount($dataSource = $modx->db->query($query2));
		}
		
		if ($limit == 0 || $limit > 1)
		{
			$this->User = false;
			return false;
		}
		else
		{
			return $modx->db->getRow($dataSource);
		}
	}

Any ideas? I’m desperate. huh

206 Posts

vhollo Reply #2, 16 years, 6 months ago

Hi,
do your users use accented characters in their usernames?

$query was the original query which doesn’t work with accented utf-8 characters in the usernames.
So I added $query2 to have access to these users; but for not being destructive I didn’t remove the original $query. (This may take the double query time.)

Now, to do so, change the function to this code:

	 /**
	 * QueryDbForUser
	 * Queries the web_users table for $_REQUEST['username'].
	 *
	 * @param string $Username The username of the user to query for.
	 * @return void
	 * @author Raymond Irving
	 * @author Scotty Delicious
	 */
	function QueryDbForUser($Username)
	{
		global $modx;
		
		$web_users = $modx->getFullTableName('web_users');
		$web_user_attributes = $modx->getFullTableName('web_user_attributes');
		
		//$query = "SELECT * FROM ".$web_users.", ".$web_user_attributes.", ".$this->CustomTable." WHERE BINARY LOWER(".$web_users.".username) = '".strtolower($Username)."' AND ".$web_user_attributes.".`internalKey` = ".$web_users.".`id` AND ".$this->CustomTable.".`internalKey` = ".$web_users.".`id`";
		$query2 = "SELECT * FROM ".$web_users.", ".$web_user_attributes.", ".$this->CustomTable." WHERE(".$web_users.".username) = '".$Username."' AND ".$web_user_attributes.".`internalKey` = ".$web_users.".`id` AND ".$this->CustomTable.".`internalKey` = ".$web_users.".`id`";
		//if (!$limit = $modx->db->getRecordCount($dataSource = $modx->db->query($query))) 
		$limit = $modx->db->getRecordCount($dataSource = $modx->db->query($query2));
		
		if ($limit == 0)
		{
			//$query = "SELECT * FROM ".$web_users.", ".$web_user_attributes." WHERE BINARY LOWER(".$web_users.".username) = '".strtolower($Username)."' AND ".$web_user_attributes.".`internalKey` = ".$web_users.".`id`";
			$query2 = "SELECT * FROM ".$web_users.", ".$web_user_attributes." WHERE(".$web_users.".username) = '".$Username."' AND ".$web_user_attributes.".`internalKey` = ".$web_users.".`id`";
			//if (!$limit = $modx->db->getRecordCount($dataSource = $modx->db->query($query))) 
			$limit = $modx->db->getRecordCount($dataSource = $modx->db->query($query2));
		}
		
		if ($limit == 0 || $limit > 1)
		{
			$this->User = false;
			return false;
		}
		else
		{
			return $modx->db->getRow($dataSource);
		}
	}

Please let me know if that helps!

Lucas