AbanteCart Community
Shopping Cart Operations => Support => General Support => Topic started by: Ollie on February 04, 2016, 06:14:06 AM
-
Some of my database search queries are taking too long. Is there a way to improve its performance?
I see a bunch of abc tables at the top. Are they necessary for Abantecart to work properly? Can they be deleted without affecting the performance of the shopping cart?
I appreciate whatever information is provided to help me improve performance/speed?
Also, I have a slow query log file, but I don't know how to read it. Any insights on that?
-
Some of my database search queries are taking too long. Is there a way to improve its performance?
Hi. Can you post what queries are slow?
-
Some of my database search queries are taking too long. Is there a way to improve its performance?
Hi. Can you post what queries are slow?
I upgraded my hosting plan and it seems to have improved as far as the query speed. Most of them took less and 1.5 sec. I found a couple that took longer. See below:
# Mon Feb 8 15:05:17 2016
# Thread_id: 684639 Schema: xxx_liveperson Last_errno: 0 Killed: 0
# Query_time: 1.924218 Lock_time: 0.000038 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1 Rows_read: 1
# Bytes_sent: 52
use xxx_liveperson;
SET timestamp=1454969117;
UPDATE livehelp_users SET cookieid='7ba998a5b51db0da3e7fdf88bc70b735',identity='208.184.161-
cslhVISITOR',ipaddress='208.184.161.172',cookied='Y',new_session='N',lastaction='20160208220515',expires='20160208222515'
WHERE sessionid='785c0a545d1655d5e3b70261d0e05a0d'
# Sun Feb 7 21:20:52 2016
# Thread_id: 247482 Schema: xxx_abantecart Last_errno: 0 Killed: 0
# Query_time: 14.013470 Lock_time: 0.000076 Rows_sent: 16 Rows_examined: 2152 Rows_affected: 0 Rows_read: 2152
# Bytes_sent: 452
use xxx_abantecart;
SET timestamp=1454905252;
SELECT DISTINCT p.manufacturer_id, m.name
FROM products p
LEFT JOIN manufacturers m ON p.manufacturer_id = m.manufacturer_id
WHERE p.product_id IN (SELECT DISTINCT ptc.product_id
FROM products_to_categories ptc
WHERE ptc.category_id IN (64, 307))
-
Hi. Can you post what queries are slow?
I also got a critical error message when using Advanced Search. Here's the error log:
2016-02-08 20:38:19 - App Error: AbanteCart core v.1.2.0 MySQL class error: Try to escape non-string value: array (
0 => 'additional receivers',
1 => 'tv/audio listening',
) in /home2/xxx/public_html/store/core/database/mysql.php on line 128
2016-02-08 20:38:19 - database error: AbanteCart core v.1.2.0 Error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
Error No: 1104
SELECT pt.tag as name, count(DISTINCT p.product_id) as total_products FROM products p
INNER JOIN product_descriptions pd
ON (p.product_id = pd.product_id AND pd.language_id = 1 ) INNER JOIN products_to_categories p2c
ON (p.product_id = p2c.product_id) INNER JOIN product_tags pt ON (p.product_id = pt.product_id and pt.language_id = '1') INNER JOIN product_tags p2t ON (p.product_id = p2t.product_id and p2t.language_id = '1') INNER JOIN product_tags p2t0 ON (p.product_id = p2t0.product_id and p2t0.language_id = '1' AND LCASE(p2t0.tag) = 'additional receivers') INNER JOIN product_tags p2t1 ON (p.product_id = p2t1.product_id and p2t1.language_id = '1' AND LCASE(p2t1.tag) = 'tv/audio listening') WHERE p.date_available <= NOW() AND p.status = '1' AND p2c.category_id = '67' AND p.manufacturer_id = '36' GROUP BY pt.tag in <b>/home2/xxx/public_html/store/core/database/mysql.php</b> on line <b>114</b>
-
1. you should switch ON amysqli database-driver (just open your system/config.php file and replace 'mysql' with 'amysqli').
2. replace file /code/database/amysqli.php by attached
-
1. you should switch ON amysqli database-driver (just open your system/config.php file and replace 'mysql' with 'amysqli').
2. replace file /code/database/amysqli.php by attached
Should I change to "amysqli" even though I'm using version 1.2.0?
-
Should I change to "amysqli" even though I'm using version 1.2.0?
yes.
simple mysql-driver is deprecated since php v.5.5 and will be removed.
use amysqli instead, or apdomysql since Abantecart v.1.2.5
-
Should I change to "amysqli" even though I'm using version 1.2.0?
yes.
simple mysql-driver is deprecated since php v.5.5 and will be removed.
use amysqli instead, or apdomysql since Abantecart v.1.2.5
Something weird happened and I'm not sure if it's related to the changes I made following your suggestion. Four new orders are not showing in Abantecart. It's as if they do not exist. I got an email confirmation for them but they are not showing under Sales > Orders. They were in the download file so I had to reupload them to show in the right place. I cleared cache, tried a couple new orders and this time I got an email confirmation for each. Any idea why this happened?
-
Something weird happened and I'm not sure if it's related to the changes I made following your suggestion. Four new orders are not showing in Abantecart. It's as if they do not exist. I got an email confirmation for them but they are not showing under Sales > Orders. They were in the download file so I had to reupload them to show in the right place. I cleared cache, tried a couple new orders and this time I got an email confirmation for each. Any idea why this happened?
it's not related to db-driver.
try to find this "mystic" order inside table orders, via phpMyAdmin. You should to know order_id. Check order_status_id field.
-
it's not related to db-driver. try to find this "mystic" order inside table orders, via phpMyAdmin. You should to know order_id. Check order_status_id field.
I found them all in the database. Amazing how the performance improved. The database is much faster now. Thank you for your help.
I have another database-related question. Yesterday, I installed v 1.2.5 (while still keeping v 1.2.0), and after toying with it for a while I realized it would be too time consuming to proceed. So I deleted "store2" which was associated with v 1.2.5. However, I noticed that all the sample products were imported into my regular database. I'd like to delete all the data associated with the sample store. Here are my questions:
Is all the data in the abc_ tables related to the sample store? (and not my regular store)
If that's the case, I'd like to delete this unnecessary data since it's just taking disk space. In fact, I already deleted the data from the three top tables. When I deleted the data from abc_addresses, for example, I noticed that some related table information remained (e.g. address_id, customer_id, company etc). See attached screenshot. Can I delete this information too?
-
it's not related to db-driver. try to find this "mystic" order inside table orders, via phpMyAdmin. You should to know order_id. Check order_status_id field.
I found them all in the database. Amazing how the performance improved. The database is much faster now. Thank you for your help.
I have another database-related question. Yesterday, I installed v 1.2.5 (while still keeping v 1.2.0), and after toying with it for a while I realized it would be too time consuming to proceed. So I deleted "store2" which was associated with v 1.2.5. However, I noticed that all the sample products were imported into my regular database. I'd like to delete all the data associated with the sample store. Here are my questions:
Is all the data in the abc_ tables related to the sample store? (and not my regular store)
If that's the case, I'd like to delete this unnecessary data since it's just taking disk space. In fact, I already deleted the data from the three top tables. When I deleted the data from abc_addresses, for example, I noticed that some related table information remained (e.g. address_id, customer_id, company etc). See attached screenshot. Can I delete this information too?
we recommends to remove your sample customers via Abantecart admin (for ex from grid). There cascade deleting. It will delete all customers data in single pass.
You can find more details about sample data in "install" directory of AbanteCart distibutive, see file abantecart_sample_data.sql
-
we recommends to remove your sample customers via Abantecart admin (for ex from grid). There cascade deleting. It will delete all customers data in single pass.
You can find more details about sample data in "install" directory of AbanteCart distibutive, see file abantecart_sample_data.sql
I looked in Admin and I don't see any samples customers listed in there. Leaving that issue aside for now, I'm having some serious issues with my website. For some reason, it's not displaying the way it should. The layout is messed up; it's showing a mix of Chinese, English and Spanish for the text; the product names are not showing and the product dropdown menus are either missing or incomplete. I tried to attach the screenshots, but I couldn't. Below are the image links. I wonder if these problems are related to the changes I made (mysql to amysqli). I tried reverting back to mysql, but it didn't solve the problem so I put config back to amysqli and replaced the corresponding file. When I logged into Admin, it was all in Chinese!
Good display: https://assistech.com/store/image/screenshot-good.jpg
Bad display: https://assistech.com/store/image/screenshot-bad1.jpg
Bad display: https://assistech.com/store/image/screenshot-bad2.jpg
-
Hello.
It looks like some translations are missing. First clear AbanteCart cache and then if issues not solved you can try to Load missing language data from your base language English. You can run this in Admin > System > Localization > Language > Edit language page.
Do not forget to backup.
-
Hello.
It looks like some translations are missing. First clear AbanteCart cache and then if issues not solved you can try to Load missing language data from your base language English. You can run this in Admin > System > Localization > Language > Edit language page.
Do not forget to backup.
Last night, I cleared cache but it didn't solve the problem. This morning, the website seems to be running okay. This is the second time it happens in a matter of a week. I wonder if the issue is related to the changes I made (from mysql to amysqli). I don't use the Abantecart translation feature. I use a third party company but I don't believe the problem is related to that. It should have no interference on the English portion of the website and yet when I tried to access English last night, it was showing a mix of three different languages on the homepage. What do you recommend at this point so this problem doesn't keep reoccuring? Here are some other changes that took place recently:
- Upgraded hosting package to Cloud Business Pro (with three data centers)
- Renamed the database (since I was trying to import an identical copy of it); changed the name back to what it was; tested the shopping cart and didn't see any problem at the time.
- Changed config.php from mysql to amysqli and replaced the amysqli file (as suggested by Abolabo)
PS: I spoke with my host this morning and they suggested restoring the website prior to the date when I was having this problem. At the same time, I had Cloudflare purge the cached version of the website. I'm waiting to see what happens. I appreciate your feedback on this.
-
language definitions and descriptions are not the same!
Just open any category in admin and look into name field for english and chinese variants. You will see that chinese value is empty. Name is "description" of your category and you responses for this data.
Other text, that built-in Abantecart calls language definitions and can be changed via system->localization->language definitions. Probably some definitions are missed. You can enable warnings in settings about that and catch all of them.
-
language definitions and descriptions are not the same!
Just open any category in admin and look into name field for english and chinese variants. You will see that chinese value is empty. Name is "description" of your category and you responses for this data.
Other text, that built-in Abantecart calls language definitions and can be changed via system->localization->language definitions. Probably some definitions are missed. You can enable warnings in settings about that and catch all of them.
I'm familiar with language definitions. As I said, I don't use Abantecart's translation. I use a third party translation service that translates my website. With this service, I don't need to use language definitions. I'm curious as to why I was having those problems. There's more than just translation. The page layout was also messed up and the category menus were incomplete or missing. Can you talk to other core developers to see if you guys can help me figure out why this happened? Might this have been caused by changing mysql to amysqli? I put it back to mysql and so far it looks good.
-
I had Cloudflare purge the cached version of the website. I'm waiting to see what happens. I appreciate your feedback on this.
Hello. Cloudflare CDN can be a problem and this explain languages messed up (as well as issue self resolve). Try to decrease Cloudflare caching level (turn OFF Autominify and scripts caching, Cloudflare Rocket loader ...etc)
-
Hello. Cloudflare CDN can be a problem and this explain languages messed up (as well as issue self resolve). Try to decrease Cloudflare caching level (turn OFF Autominify and scripts caching, Cloudflare Rocket loader ...etc)
Ever since I purged cache on CloudFlare, restored my files and changed back to mysql, my shopping cart stabilized. I haven't seen the problem yesterday or today. I'll see about making the changes you recommended on CloudFlare. Also, I'd like to change the config setting from mysql back to amysqli (since this seems to be faster), but I'm afraid that this might bring those performance issues back. Can you tell me for sure that those problems were not being caused by the changes I made? Originally, I had the config file set to mysql, which was changed to amysqli, which was replaced with a new file provided by Abolabo. Thanks for your assistance.
-
In my opinion languages messed up by Cloudflare. I use it some time before and it is good service but if cache only static content like images. Other optimizers/loader usually give unexpected results.
-
In my opinion languages messed up by Cloudflare. I use it some time before and it is good service but if cache only static content like images. Other optimizers/loader usually give unexpected results.
As I said, the problem was more than just translation. The layout was also messed up, including Category menus that were either missing or incomplete. Can you answer this question:
Can you tell me for sure that those problems were not being caused by the changes I made? Originally, I had the config file set to mysql, which was changed to amysqli, which was replaced with a new file provided by Abolabo. I need to know with a 100% certainty that this was not the reason for the problem.
PS: Website is slow again as I'm still using mysql instead of amysqli.
-
PS: Website is slow again as I'm still using mysql instead of amysqli.
i really do not understand how relates your custom translation solution with database driver.
-
PS: Website is slow again as I'm still using mysql instead of amysqli.
i really do not understand how relates your custom translation solution with database driver.
The problem was not just translation, as I explained in my previous post. Are you saying that those problems have nothing to do with changing from mysql to amysqli?
-
The problem was not just translation, as I explained in my previous post. Are you saying that those problems have nothing to do with changing from mysql to amysqli?
please do few tests with https://developers.google.com/speed/pagespeed/insights/ with both db-drivers and post page load time here.
i need a proofs:)
-
please do few tests with https://developers.google.com/speed/pagespeed/insights/ with both db-drivers and post page load time here.
i need a proofs:)
I need to know for sure that I won't have the same problems if I change to amysql. Can you please answer my previous question?
-
it's not related to db-driver.
i answered earlier.
-
it's not related to db-driver.
i answered earlier.
Sorry, I'm not familiar with "db-driver". Does this mean that the problem was not caused by me changing to amysqli? Can I safely use it with version 1.2.0? I'm trying to avoid the same problems I was having before.
-
Sorry, I'm not familiar with "db-driver". Does this mean that the problem was not caused by me changing to amysqli? Can I safely use it with version 1.2.0? I'm trying to avoid the same problems I was having before.
Hello. "db-driver" mean Database driver type. Sorry for inconvenience.
In any way amysqli (or MySQLi ) is a MySQL Improved driver. You should use MySQLi or PDO_MySQL (another db driver) because MySQL is officially deprecated and will be removed in the future PHP servers.
http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php
-
Hello. "db-driver" mean Database driver type. Sorry for inconvenience.
In any way amysqli (or MySQLi ) is a MySQL Improved driver. You should use MySQLi or PDO_MySQL (another db driver) because MySQL is officially deprecated and will be removed in the future PHP servers.
http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php
Thank you for the heads-up. I changed to amysqli and so far it's working great. None of the problems I had before.