COMMUNITY FORUM

Community Forum
  1. Forum
  2. K2 Community Forum
  3. English K2 Community
  4. k2_items innodb

k2_items innodb

English K2 Community 7 replies 1557 views
Павел Абакумов 7 years 2 weeks ago #173457
New K2 installations are used for innodb materials, but I have myisam. This is reflected in the speed of the site. How can I migrate correctly? Is there any manual on migration?
JoomlaWorks Admin 7 years 2 weeks ago #173458
MyISAM is the default storage engine set in your MySQL/MariaDB server or just the storage engine used in the K2 tables? Your question is not clear...
Павел Абакумов Topic Author 7 years 1 week ago #173497
I have a k2 component base in myisam. The installation was done a long time ago. The database is about 60,000 materials. How to migrate base to innodb?

How can I update tables in the database?

My database server uses mariadb 10.1

www.joomlaworks.net/forum/k2-en/35853-convert-tables-from-myisam-2-innodb-4-performance

Can the community help with mysql migration request?

Edited 7 years 1 week ago by Павел Абакумов

JoomlaWorks Admin 7 years 1 week ago #173503
First off all, run a MyISAM to InnoDB converter script like this: pantheon.io/docs/myisam-to-innodb

Such a script will change any MyISAM table that CAN be switched to InnoDB.

2 tables in K2 won't be switched and for the 2 tables you need to run some SQL commands in phpMyAdmin or a similar DB administration tool. Copy/paste the lines below into some code/text editor and then replace XYZ with your actual DB prefix, then copy/paste the changed lines into phpMyAdmin's SQL tab after you switch to your K2 database. Depending on your server specs, this may take a while. And make sure you grab both a full and also a partial backup of your database (just the 2 tables what will be affected). If sh*t happens, you'll be able to re-import them.

The SQL to execute is this:
ALTER TABLE `XYZ_k2_items` DROP INDEX `search`;
ALTER TABLE `XYZ_k2_items` DROP INDEX `title`;

ALTER TABLE `XYZ_k2_items` ADD INDEX `access` (`access`);
ALTER TABLE `XYZ_k2_items` ADD INDEX `published` (`published`);
ALTER TABLE `XYZ_k2_items` ADD INDEX `publish_down` (`publish_down`);
ALTER TABLE `XYZ_k2_items` ADD INDEX `publish_up` (`publish_up`);
ALTER TABLE `XYZ_k2_items` ADD INDEX `trash` (`trash`);
ALTER TABLE `XYZ_k2_items` ENGINE=InnoDB;

ALTER TABLE `XYZ_k2_tags` DROP INDEX `name`;

ALTER TABLE `XYZ_k2_tags` ENGINE=InnoDB;
Павел Абакумов Topic Author 7 years 6 days ago #173547
Better not. On myisam even faster. Here there is a terrible runtime request. Is there anything you can do about this? The patch applied www.joomlaworks.net/forum/k2-en/47858-solved-k2-query-cache-problem#163417.

Request time: 1852.93 ms After the last request: 1.00 ms Request memory: 0.026 MB Memory before the request: 15.592 MB Rows selected: 1
SELECT COUNT(*)
FROM xyz_k2_items as i RIGHT JOIN xyz_k2_categories c
ON c.id = i.catid
WHERE i.published = 1
AND i.access IN(1,1,5)
AND i.trash = 0
AND c.published = 1
AND c.access IN(1,1,5)
AND c.trash = 0
AND c.language IN ('ru-RU', '*')

AND i.language IN ('ru-RU', '*')
AND ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2019-11-08 12:17:57' )
AND ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2019-11-08 12:17:57' )
AND c.id IN (1,2,4,36,37,104,105,106,108,109,111,126)

This is on a localhost using mariadb (innodb). The working server is more powerful.
JoomlaWorks Admin 7 years 6 days ago #173548
There have been performance improvements in the upcoming K2 v2.10. Grab the dev version from github.com/getk2/k2/archive/master.zip and install on top of 2.9.0, then test again.

I haven't tested the FORCE INDEX (idx) option to be honest.
Павел Абакумов Topic Author 7 years 6 days ago #173549
K2 v2.10.0 [Dev Build 20191106] The patch applied www.joomlaworks.net/forum/k2-en/47858-solved-k2-query-cache-problem#163417 . Not much faster. This is the best localhost result with the patch and myisam tables applied.

Request time: 1228.11 ms After the last request: 0.20 ms Request memory: 0.359 MB Memory before the request: 16.917 MB Rows selected: 30
SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias, c.params AS categoryparams
FROM xyz_k2_items as i FORCE INDEX (item) RIGHT JOIN xyz_k2_categories AS c
ON c.id = i.catid
WHERE i.published = 1
AND i.access IN(1,5)
AND i.trash = 0
AND c.published = 1
AND c.access IN(1,5)
AND c.trash = 0
AND c.language IN('ru-RU', '*')
AND i.language IN('ru-RU', '*')
AND (i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2019-11-08 19:38:06')
AND (i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2019-11-08 19:38:06')
AND c.id IN(1,2,4,36,37,104,105,106,108,109,111,126)
ORDER BY i.publish_up DESC
LIMIT 0, 30

Database at innodb

Request time: 1996.04 ms After the last request: 0.20 ms Request memory: 0.359 MB Memory before the request: 16.918 MB Rows selected: 30
SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias, c.params AS categoryparams
FROM xyz_k2_items as i USE INDEX (item) RIGHT JOIN xyz_k2_categories AS c
ON c.id = i.catid
WHERE i.published = 1
AND i.access IN(1,5)
AND i.trash = 0
AND c.published = 1
AND c.access IN(1,5)
AND c.trash = 0
AND c.language IN('ru-RU', '*')
AND i.language IN('ru-RU', '*')
AND (i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2019-11-08 19:38:47')
AND (i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2019-11-08 19:38:47')
AND c.id IN(1,2,4,36,37,104,105,106,108,109,111,126)
ORDER BY i.publish_up DESC
LIMIT 0, 30
JoomlaWorks Admin 7 years 5 days ago #173552
On a production server, InnoDB is much faster for practical reasons.

Log in or create an account to reply.

RSS feed