On this stage it's required to look if it's possible to speed up the X-Cart without big changes. And it's better to check the sql queries on this stage. we need to know how much and how much time it's executed.
We can check in here, by the following code:
include/func/func.db.php
function db_query($query) {
$start_time = func_microtime();
....
global $sql_counter;
$sql_counter ++;
if ($_GET['cnt']) {
echo $sql_counter.' '.$query.' '.($t_end - $start_time).'<br>';
}
#
# Auto repair
#
....
}
and run the home, category and product page with the “cnt” get param.
If you see the slow queries (with 0.1 sec time and bigger) - it's most simple case. You should simply make the “explain” command and add the appropriate keys (please read the mysql manual if you are not sure here).
The second thing is amount of queries. If you see 100 or more queries (what is standart for the x-cart), it's required to find the way to decrease it's amount by caching for example. It's importand because if there are 200 question per page and you've got the 10 page loading per sec, the total amount of question is 2000 and it's enough to slow down or float your server.
With the justbedding.com.au the query time is ok, there were a lot of queries - about 400 per page. Mostly it's related with the modules. Here I will describe only some of the general things.
Firstly the following queries was removed and the variables have been hard coded. Yes, the time of these queries is small, but it hasn't got any sense to execute it on each page loading. And since the server has got the stable configuration - it hasn't got any sense to cache it
SHOW VARIABLES LIKE 'max_allowed_packet' 0.000674962997437 # init.php SHOW VARIABLES LIKE 'max_join_size' 0.00038480758667
Then all of the question to the xcart_session_history was disabled:
UPDATE xcart_session_history SET `xid`='030aeb3983cdd673a9a1799a80b235f7', `dest_xid`='' WHERE ip = '89.250.160.108' AND host = 'www.justbedding.com.au' 0.00037693977356
Investigation of the meaning of this table gives this:
# config.php
# The constant USE_SESSION_HISTORY allows you to enable synchronization of
# user sessions on the main website of your store and on domain aliases.
#
define("USE_SESSION_HISTORY", true);
It's not possible to way why it's enabled by default, when it's not used in most of the cases. So it was switched to false.
Additionally a few update session queries are executed on the same page.
UPDATE xcart_sessions_data SET data=...
Actually there are no any sense to store the same session a few times on the same page. All of the additional x_session_save was found and removed.
It's possible to remove this from the init.php, it it will be done as the default mysql setting. there are no big sense to execute this query each time the X-Cart runs.
SET sql_mode = 'MYSQL40'
Some of this can be simply cached. For example the config variables (usually it's changed very rare). Please note that when you save the variable to the cache you save the time twice - the sql query time and the time is spent on the array creation.
Here the config query:
SELECT name, value, category FROM xcart_config WHERE type != 'separator' 0.000555992126465 # init.php
Since it's going to be cached, the init.php was changes in the following way:
....
global $memcache;
$memcache = new Memcache;
$memcache->connect('localhost', 11211);
$config = $memcache->get('config');
if (!$config) {
$c_result = db_query("SELECT name, value, category FROM $sql_tbl[config] WHERE type != 'separator'");
$config = array();
if ($c_result)
while ($row = db_fetch_row($c_result)) {
if (!empty($row[2]))
$config[$row[2]][$row[0]] = $row[1];
else
$config[$row[0]] = $row[1];
}
db_free_result($c_result);
$memcache->set('config', $config);
}
....
You can notice that the memcache Apache module is used. Of course it have to be installed before you make any changes here.
With the config memcache the include/data_cache.php should be changed to include the cache set:
if (empty($config['data_cache_expiration']) || (time()-$config['data_cache_expiration']) > 3600) {
...
$memcache->set('config', $config);
}
The same the following information should be cached too:
Some of the things are specific for the modules or more over - for the investigated store. They are mentioned below, since it an be helpfull for you.
In the investigation the product_meta.php gives the amazing result - there are up to 6 queries to fill 3 fields - keywords/description and title. The product_meta.php can be completely changed from this
$titlefield = func_query_first_cell("select value from product_keywords where name='extrafield_title'");
$keywordfield = func_query_first_cell("select value from product_keywords where name='extrafield_keywords'");
$descriptionfield = func_query_first_cell("select value from product_keywords where name='extrafield_description'");
$product_meta_title = func_query_first_cell("select efv.value from $sql_tbl[extra_field_values] as efv, $sql_tbl[extra_fields] as ef where efv.fieldid = ef.fieldid and productid='$productid' and field='$titlefield'");
$product_meta_keywords = func_query_first_cell("select efv.value from $sql_tbl[extra_field_values] as efv, $sql_tbl[extra_fields] as ef where efv.fieldid = ef.fieldid and productid='$productid' and field='$keywordfield'");
$product_meta_description = func_query_first_cell("select efv.value from $sql_tbl[extra_field_values] as efv, $sql_tbl[extra_fields] as ef where efv.fieldid = ef.fieldid and productid='$productid' and field='$descriptionfield'");
to this
$meta_data = func_query_hash("select efv.value, pk.name from $sql_tbl[extra_field_values] as efv, $sql_tbl[extra_fields] as ef, product_keywords as pk where efv.fieldid = ef.fieldid and field=pk.name and pk.name in ('extrafield_title', 'extrafield_keywords', 'extrafield_description')", 'name', true, true);
as you can see one query can make the same work. But actually it's better to remove this module fully and use another SEO modules.
You can notice a lot of the queries, related wit the Special Offers module of the X-Cart. It has got the big sense to store these functions results to the cache in the modules/Special_Offers/func.php:
The result should be 0.5-1 sec for the script execution and about 100 queries per page - then it's possible to move to the layout stage