Bug VB Shop Slow Query

[Home Alone]

Shop purchases take anywhere from 2 - 90 seconds. This is from the slow query log:

# Query_time: 89.359487 Lock_time: 0.000049 Rows_sent: 355263 Rows_examined: 17523704
SET timestamp=1464027158;
SELECT
pointstable_4.reputation AS points_4,
post.userid,
user.dbtech_vbshop_purchase
FROM post AS post
LEFT JOIN user AS user ON(user.userid = post.userid)
LEFT JOIN user AS pointstable_4 ON(pointstable_4.userid = post.userid)
WHERE post.dateline > 1394470641 AND (post.visible = 1 OR post.postid = 26695098);

# Query_time: 14.194546 Lock_time: 0.000052 Rows_sent: 89360 Rows_examined: 268084
SET timestamp=1464026887;
SELECT
pointstable_4.reputation AS points_4,
post.userid,
user.dbtech_vbshop_purchase
FROM post AS post
LEFT JOIN user AS user ON(user.userid = post.userid)
LEFT JOIN user AS pointstable_4 ON(pointstable_4.userid = post.userid)
WHERE post.dateline > 1463410478 AND (post.visible = 1 OR post.postid = 26695066);

# Query_time: 5.832243 Lock_time: 0.000044 Rows_sent: 32073 Rows_examined: 96223
SET timestamp=1464026608;
SELECT
pointstable_4.reputation AS points_4,
post.userid,
user.dbtech_vbshop_purchase
FROM post AS post
LEFT JOIN user AS user ON(user.userid = post.userid)
LEFT JOIN user AS pointstable_4 ON(pointstable_4.userid = post.userid)
WHERE post.dateline > 1463783766 AND (post.visible = 1 OR post.postid = 26695007);

Reputation points are used for currency.

Any idea what causes this and if it can be fixed?

 

[Fillip H.]

I am unable to find a match for that query in any DragonByte Tech product, or vBulletin 4 itself. Are you able to provide any more information as to how this query is generated?

 

[Home Alone]

I thought it was being generated when something was purchased from the shop.

I have 4 shops. Only the 4th shop is active, so I assume pointstable_4 is referring to the 4th shop, is that correct? What is the dbtech_vbshop_purchase table?

 

[Fillip H.]

I thought it was being generated when something was purchased from the shop.

Absolutely not, there is no reason to query posts when purchasing an item, certainly not in that manner.

I have 4 shops. Only the 4th shop is active, so I assume pointstable_4 is referring to the 4th shop, is that correct?

No. The 4 refers to the currency ID.

What is the dbtech_vbshop_purchase table?

That is a column, not a table. It's a serialised array of all the items the user has purchased. Generally when it's added to a query like that, it's to enable additional Markup Username styles and additional permissions.

 

[Home Alone]

What is pointstable_4? Would it be referring to the reputation table?

 

[Fillip H.]

Sorry, I'm not sure what you're asking.

 

[Home Alone]

In this query:
SELECT
pointstable_4.reputation AS points_4,

what exactly is pointstable_4 -- is that the reputation column? The currency is setup to use the user table and reputation column.

I'm trying to find out why the site is so much slower when the shop is enabled. Even after disabling all of the items the site is much slower with the shop enabled. I thought it might be because there were too many items, but it runs the same speed whether there are 1 or 100 items.

I disabled all of the currencies and the site is running fine now. With no items and no currency, it's pretty much the equivalent of having no shop.

 

[Fillip H.]

In this query:
SELECT
pointstable_4.reputation AS points_4,

what exactly is pointstable_4 -- is that the reputation column? The currency is setup to use the user table and reputation column.

Internal variable names, it's nothing to worry about.

I'm trying to find out why the site is so much slower when the shop is enabled. Even after disabling all of the items the site is much slower with the shop enabled.

Is the Force Formatting setting in the general options enabled? If so, please try disabling it.

 

[Home Alone]

Force Formatting is not enabled.

 

[Home Alone]

Is it possible to create a new currency and not use the user table?

 

[Fillip H.]

Is it possible to create a new currency and not use the user table?

It is, the other table needs to have a userid column to link to the user table though.

 

[Home Alone]

Shop purchases take anywhere from 2 - 90 seconds. This is from the slow query log:

# Query_time: 89.359487 Lock_time: 0.000049 Rows_sent: 355263 Rows_examined: 17523704
SET timestamp=1464027158;
SELECT
pointstable_4.reputation AS points_4,
post.userid,
user.dbtech_vbshop_purchase
FROM post AS post
LEFT JOIN user AS user ON(user.userid = post.userid)
LEFT JOIN user AS pointstable_4 ON(pointstable_4.userid = post.userid)
WHERE post.dateline > 1394470641 AND (post.visible = 1 OR post.postid = 26695098);

# Query_time: 14.194546 Lock_time: 0.000052 Rows_sent: 89360 Rows_examined: 268084
SET timestamp=1464026887;
SELECT
pointstable_4.reputation AS points_4,
post.userid,
user.dbtech_vbshop_purchase
FROM post AS post
LEFT JOIN user AS user ON(user.userid = post.userid)
LEFT JOIN user AS pointstable_4 ON(pointstable_4.userid = post.userid)
WHERE post.dateline > 1463410478 AND (post.visible = 1 OR post.postid = 26695066);

# Query_time: 5.832243 Lock_time: 0.000044 Rows_sent: 32073 Rows_examined: 96223
SET timestamp=1464026608;
SELECT
pointstable_4.reputation AS points_4,
post.userid,
user.dbtech_vbshop_purchase
FROM post AS post
LEFT JOIN user AS user ON(user.userid = post.userid)
LEFT JOIN user AS pointstable_4 ON(pointstable_4.userid = post.userid)
WHERE post.dateline > 1463783766 AND (post.visible = 1 OR post.postid = 26695007);

Reputation points are used for currency.

Any idea what causes this and if it can be fixed?

This query only runs when any VBShop currency is enabled. I disabled all currencies for one day and there were no records of this in the slow query log. I enabled one currency and the query started appearing again in the slow query log. Users receive points for each post and each thread. Would that be related to this query?

 

[Fillip H.]

I'm not sure what to tell you to be honest, I can't find any mention of this query anywhere in our files nor vBulletin. It's most likely generated by a custom mod that has code in it that lets vBShop hook into it (even though it isn't intentional on my end).

 

[Fillip H.]

Have you had any luck finding the modification that causes this conflict with vBShop?

The problem is that on their own, the two parts of the query (fetching inefficiently from the Post table, and joining to the user table) run fine, but when combined they cause a massive performance problem due to how MySQL joins work.

Is there a way you can enable more advanced logging so you can see what page this query is ran at? I've not looked into the MySQL log levels so I don't know if it offers stack traces or anything of the sort - anything at all that could help us find the source would be helpful.

 

[DragonByte Technologies]

Hello Home Alone,

This ticket has now been closed with the status Cannot Reproduce.

We hope your issue or question has been addressed to your satisfaction. If not, please feel free to re-open it by clicking this link.

If you have any further issues or questions, please feel free to start a new support ticket via the button at the top of every page.

Thank you!