SOLVED-50k customer site- returning customer login takes 10 seconds

websiteworld wrote: ↑

Mon Mar 14, 2022 2:48 am

Not likely and it was doing this in the sandbed before it even went live.

websiteworld wrote: ↑

Mon Mar 14, 2022 3:28 am

I optimized the OC_Customer table and it's twice as fast. Still slower than I'd like, but much better at about 4 seconds. Thanks.

paulfeakins wrote: ↑

Tue Mar 15, 2022 12:17 am

Check against this list of possible reasons: https://www.antropy.co.uk/blog/how-to-s ... ed-issues/

IP_CAM wrote: ↑

Sat Mar 19, 2022 3:39 am

You could try this one, to possibly get some better results:
https://github.com/IP-CAM/Optimize-Data ... .3.x-v.3.x

<?php
class ControllerExtensionModuleDboptim extends Controller {
	public function index() {
		$data = array();
		
		$result = $this->db->query('show tables');
		$data['tables'] = array();
		
		foreach ($result->rows as $row) {
			$data['tables'][ $row[key($row)] ] = null;
		}
		
// 		if ($this->request->server['REQUEST_METHOD'] == 'POST' && isset($_POST['optimize']) && is_array($_POST['optimize']) && ! empty($_POST['optimize'])) {
// 			foreach($_POST['optimize'] as $table) {
					foreach ($data['tables'] as $table => $value) {
						$result = $this->db->query('optimize table '. $table);
						
						$data['tables'][$table] = $result->rows[0]['Msg_text'];
					}
// 			}
// 		}
		
		$this->load->language('extension/module/dboptim');
		
		$this->document->setTitle($this->language->get('heading_title'));
		
		$data['breadcrumbs'] = array();

		$data['breadcrumbs'][] = array(
			'text' => $this->language->get('text_home'),
			'href' => $this->url->link('common/dashboard', 'user_token=' . $this->session->data['user_token'], true)
		);

		$data['breadcrumbs'][] = array(
			'text' => $this->language->get('text_extension'),
			'href' => $this->url->link('marketplace/extension', 'user_token=' . $this->session->data['user_token'] . '&type=module', true)
		);

		if (!isset($this->request->get['module_id'])) {
			$data['breadcrumbs'][] = array(
				'text' => $this->language->get('heading_title'),
				'href' => $this->url->link('extension/module/dboptim', 'user_token=' . $this->session->data['user_token'], true)
			);
		} else {
			$data['breadcrumbs'][] = array(
				'text' => $this->language->get('heading_title'),
				'href' => $this->url->link('extension/module/dboptim', 'user_token=' . $this->session->data['user_token'] . '&module_id=' . $this->request->get['module_id'], true)
			);
		}
		
		if (!isset($this->request->get['module_id'])) {
			$data['action'] = $this->url->link('extension/module/dboptim', 'user_token=' . $this->session->data['user_token'], true);
		} else {
			$data['action'] = $this->url->link('extension/module/dboptim', 'user_token=' . $this->session->data['user_token'] . '&module_id=' . $this->request->get['module_id'], true);
		}

		$data['cancel'] = $this->url->link('marketplace/extension', 'user_token=' . $this->session->data['user_token'] . '&type=module', true);

		
		$data['header'] = $this->load->controller('common/header');
		$data['column_left'] = $this->load->controller('common/column_left');
		$data['footer'] = $this->load->controller('common/footer');

		$this->response->setOutput($this->load->view('extension/module/dboptim', $data));
	}
	
	protected function validate() {
		if (!$this->user->hasPermission('modify', 'extension/module/dboptim')) {
			$this->error['warning'] = $this->language->get('error_permission');
		}

		return !$this->error;
	}
}

websiteworld wrote: ↑

Sat Mar 19, 2022 3:56 am

How do we know if it's safe? What exactly is it doing? Here is the code:

Code: Select all

<?php
class ControllerExtensionModuleDboptim extends Controller {
	public function index() {
		$data = array();
		
		$result = $this->db->query('show tables');
		$data['tables'] = array();
		
		foreach ($result->rows as $row) {
			$data['tables'][ $row[key($row)] ] = null;
		}
		
// 		if ($this->request->server['REQUEST_METHOD'] == 'POST' && isset($_POST['optimize']) && is_array($_POST['optimize']) && ! empty($_POST['optimize'])) {
// 			foreach($_POST['optimize'] as $table) {
					foreach ($data['tables'] as $table => $value) {
						$result = $this->db->query('optimize table '. $table);
						
						$data['tables'][$table] = $result->rows[0]['Msg_text'];
					}
// 			}
// 		}
}

OPTIMIZE table

$_POST['optimize']

$result = $this->db->query('show tables');

if ($this->request->server['REQUEST_METHOD'] == 'POST'

foreach ($data['tables'] as $table => $value) {

OSWorX wrote: ↑

Sat Mar 19, 2022 11:31 am

websiteworld wrote: ↑

Sat Mar 19, 2022 3:56 am

How do we know if it's safe? What exactly is it doing? Here is the code:

Code: Select all

<?php
class ControllerExtensionModuleDboptim extends Controller {
	public function index() {
		$data = array();
		
		$result = $this->db->query('show tables');
		$data['tables'] = array();
		
		foreach ($result->rows as $row) {
			$data['tables'][ $row[key($row)] ] = null;
		}
		
// 		if ($this->request->server['REQUEST_METHOD'] == 'POST' && isset($_POST['optimize']) && is_array($_POST['optimize']) && ! empty($_POST['optimize'])) {
// 			foreach($_POST['optimize'] as $table) {
					foreach ($data['tables'] as $table => $value) {
						$result = $this->db->query('optimize table '. $table);
						
						$data['tables'][$table] = $result->rows[0]['Msg_text'];
					}
// 			}
// 		}
}

Beside the former comment: yes it's safe.
And what this code does:
Running a simple

Code: Select all

OPTIMIZE table

which is a native MySQL command.

on previous selected tables (in a form):

Code: Select all

$_POST['optimize']

btw: if you already switched your tables from MyISAM to InnoDB, the OPTIMIZE command is useless.

But it's funny to see "a well coded script" ..

At the beginning you get the tables with that:

Code: Select all

$result = $this->db->query('show tables');

later you check if something is coming from a form (here commented)

Code: Select all

if ($this->request->server['REQUEST_METHOD'] == 'POST'

and then you use this:

Code: Select all

foreach ($data['tables'] as $table => $value) {

(using the former query).

Generally it would be better (and faster) not to call each table, instead use the IN syntax on one call.
And please, not show tables should be SHOW TABLES (optimize the same).

paulfeakins wrote: ↑

Tue Mar 15, 2022 12:17 am

Check against this list of possible reasons: https://www.antropy.co.uk/blog/how-to-s ... ed-issues/

ALTER TABLE `category` ADD INDEX ( `parent_id` );
ALTER TABLE `category` ADD INDEX ( `top` );
ALTER TABLE `category` ADD INDEX ( `sort_order` );
ALTER TABLE `category` ADD INDEX ( `status` );
ALTER TABLE `option` ADD INDEX ( `sort_order` );
ALTER TABLE `option_description` ADD INDEX ( `name` );
ALTER TABLE `option_value` ADD INDEX ( `option_id` );
ALTER TABLE `option_value_description` ADD INDEX ( `option_id` );
ALTER TABLE `order` ADD INDEX ( `customer_id` );
ALTER TABLE `product` ADD INDEX ( `model` );
ALTER TABLE `product` ADD INDEX ( `sku` );
ALTER TABLE `product` ADD INDEX ( `upc` );
ALTER TABLE `product` ADD INDEX ( `manufacturer_id` );
ALTER TABLE `product` ADD INDEX ( `sort_order` );
ALTER TABLE `product` ADD INDEX ( `status` );
ALTER TABLE `product_option` ADD INDEX ( `option_id` );
ALTER TABLE `product_option_value` ADD INDEX ( `product_option_id` );
ALTER TABLE `product_option_value` ADD INDEX ( `product_id` );
ALTER TABLE `product_option_value` ADD INDEX ( `option_id` );
ALTER TABLE `product_option_value` ADD INDEX ( `option_value_id` );
ALTER TABLE `product_tag` ADD INDEX ( `product_id` );
ALTER TABLE `product_tag` ADD INDEX ( `tag` );
ALTER TABLE `url_alias` ADD INDEX ( `query` );
ALTER TABLE `url_alias` ADD INDEX ( `keyword` );
ALTER TABLE `user` ADD INDEX ( `username` );
ALTER TABLE `user` ADD INDEX ( `password` );
ALTER TABLE `user` ADD INDEX ( `email` );

straightlight wrote: ↑

Sat Mar 19, 2022 9:22 pm

Nowhere to be said it wasn't safe. What was said in there is that running the script such ways would be pointless.

websiteworld wrote: ↑

Sat Mar 19, 2022 3:56 am

How do we know if it's safe? What exactly is it doing?

websiteworld wrote: ↑

Sat Mar 19, 2022 9:41 pm

paulfeakins wrote: ↑

Tue Mar 15, 2022 12:17 am

Check against this list of possible reasons: https://www.antropy.co.uk/blog/how-to-s ... ed-issues/

For future reference adding indexes solved my issue. The site is lightning fast now! Product_tag was skipped in my case.

Code: Select all

ALTER TABLE `category` ADD INDEX ( `parent_id` );
ALTER TABLE `category` ADD INDEX ( `top` );
ALTER TABLE `category` ADD INDEX ( `sort_order` );
ALTER TABLE `category` ADD INDEX ( `status` );
ALTER TABLE `option` ADD INDEX ( `sort_order` );
ALTER TABLE `option_description` ADD INDEX ( `name` );
ALTER TABLE `option_value` ADD INDEX ( `option_id` );
ALTER TABLE `option_value_description` ADD INDEX ( `option_id` );
ALTER TABLE `order` ADD INDEX ( `customer_id` );
ALTER TABLE `product` ADD INDEX ( `model` );
ALTER TABLE `product` ADD INDEX ( `sku` );
ALTER TABLE `product` ADD INDEX ( `upc` );
ALTER TABLE `product` ADD INDEX ( `manufacturer_id` );
ALTER TABLE `product` ADD INDEX ( `sort_order` );
ALTER TABLE `product` ADD INDEX ( `status` );
ALTER TABLE `product_option` ADD INDEX ( `option_id` );
ALTER TABLE `product_option_value` ADD INDEX ( `product_option_id` );
ALTER TABLE `product_option_value` ADD INDEX ( `product_id` );
ALTER TABLE `product_option_value` ADD INDEX ( `option_id` );
ALTER TABLE `product_option_value` ADD INDEX ( `option_value_id` );
ALTER TABLE `product_tag` ADD INDEX ( `product_id` );
ALTER TABLE `product_tag` ADD INDEX ( `tag` );
ALTER TABLE `url_alias` ADD INDEX ( `query` );
ALTER TABLE `url_alias` ADD INDEX ( `keyword` );
ALTER TABLE `user` ADD INDEX ( `username` );
ALTER TABLE `user` ADD INDEX ( `password` );
ALTER TABLE `user` ADD INDEX ( `email` );

OSWorX wrote: ↑

Sat Mar 19, 2022 9:48 pm

straightlight wrote: ↑

Sat Mar 19, 2022 9:22 pm

Nowhere to be said it wasn't safe. What was said in there is that running the script such ways would be pointless.

Guess you have to buy new glasses .. ?!

websiteworld wrote: ↑

Sat Mar 19, 2022 3:56 am

How do we know if it's safe? What exactly is it doing?

Please read better next time before wasting the space here.