[#7997] Some Communiy Builder Running which does not clear

3 years 10 months ago - 3 years 10 months ago #318266 by edmundcheong
A qurey is running at the back and it is piling up without clearing up. It's status is sending..
SELECT u.`id` FROM `#_comprofiler` AS c INNER JOIN `#_users` AS u ON u.`id` = c.`id` WHERE ( u.`username` = 'xxxxxxx' OR c.`alias` = 'xxxxxxx' ) LIMIT 0, 1

Please Log in to join the conversation.

3 years 10 months ago #318278 by krileon
I've no idea what you mean by this. The query doesn't clear? Queries don't pile up. That's the query for checking if a user exists so it will be executed several times during registration or username edit. It's a part of the username field and can be disabled within CB > Configuration > Registration by setting "Ajax Username Checker" to "No".


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

3 years 10 months ago - 3 years 10 months ago #318292 by edmundcheong
Replied by edmundcheong on topic Some Communiy Builder Running which does not clear
Sorry for not being clear on this. I just needed to know what is running the queries as we are having high traffic to our site.

Somehow is the query becoming a bottleneck as it takes over 1.4 seconds to give a return to the query since we have over 300,000 registered users..

I may need to amend the query and can you tell me where to do it? But I will be fully responsible if anything happens to the core.

I might simplify it to be:
SELECT u.`id` FROM `#_comprofiler` AS c INNER JOIN `#_users` AS u ON u.`id` = c.`id` WHERE ( u.`username` = 'XXXXX') LIMIT 0, 1

or mebbe to even simplify it more

SELECT `id` FROM `#_comprofiler` WHERE `username` = 'XXXXX' LIMIT 0, 1

The second query took 0.0017 seconds compared to a 1.4 seconds to the first query.

Please Log in to join the conversation.

3 years 10 months ago #318316 by krileon
It maybe faster for us to just do the two queries separately instead of using a join. It is indeed slow on my test install with 1 million users (takes 4 seconds). Have added a bug ticket to review improving this.

forge.joomlapolis.com/issues/7997


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

3 years 10 months ago - 3 years 10 months ago #318340 by edmundcheong
Replied by edmundcheong on topic Some Communiy Builder Running which does not clear
In the meantime, can you tell me the location to update this query at registration? Might need a quick fix and i am running a mass registration which is looking at 1,339 registration concurrently.. fyi, it's a mass registration for a COVID-19 screening.. so you can imagine the load..

Please Log in to join the conversation.

3 years 10 months ago #318342 by edmundcheong
Replied by edmundcheong on topic Some Communiy Builder Running which does not clear
is this the only location:
if ( $_CB_database->isDbCollationCaseInsensitive() ) {
			$query	.=	"\n WHERE ( u." . $_CB_database->NameQuote( 'username' ) . " = " . $_CB_database->Quote( trim( $username ) )
					.	' OR c.' . $_CB_database->NameQuote( 'alias' ) . ' = ' . $_CB_database->Quote( trim( $username ) ) . ' )';

or there are anywhere else in the code which i need to update?

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.356 seconds

Facebook Twitter LinkedIn