We use MariaDB which is a very good evolution of MySQL server, and there is no compatibility issue between databases under MySQL to MariaDB. With MariaDB, it is suggested to convert the MyISAM tables to the new Aria format, as this gives better crash resistance, and a little more speed. The conversion to innoDB is the second option.

Converting tables is a very basic task of database optimization, the real optimization is to have indexes in your database, and this is hardly accessible in existing CMS, you can only suggest third party providers of modules to improve it. For custom websites, only a database specialist or high level webmaster can do this very technical job... please refer to online tutorials for this.

Choose one database, filter tables by size in MB, but also after by number of lines they could contain, and analyze the reason being of this table, if myslam, you can convert to innodb, and/or, if cache/logs related : you can also purge it now and regularly... If none apply : the issue may remain in the conception of your database, tables of certain plugins.

Before converting, use simple tools of phpmyadmin first, select all tables of one database : verify table, repair table, and optimize table...

 

How to convert from MyISAM to Aria or InnoDB ?

To convert for example all existing MyISAM to InnoDB. From phpmyadmin, click on left on database you want, and click on SQL at the top. Paste this below, then 'Execute'

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') 

FROM INFORMATION_SCHEMA.TABLES

WHERE ENGINE='MyISAM'

 

The result displayed by default with short texts, it is therefore imperative to go to the top in '+ Options', click 'Complete texts', and get this in this example : 

ALTER TABLE ps_carrier ENGINE=InnoDB;

ALTER TABLE ps_expressmailing ENGINE=InnoDB;

ALTER TABLE ps_expressmailing_email ENGINE=InnoDB;

ALTER TABLE ps_expressmailing_email_accountdates ENGINE=InnoDB;

ALTER TABLE ps_expressmailing_email_birthdays ENGINE=InnoDB;

ALTER TABLE ps_expressmailing_email_buyingdates ENGINE=InnoDB;

ALTER TABLE ps_expressmailing_email_categories ENGINE=InnoDB;

ALTER TABLE ps_expressmailing_email_civilities ENGINE=InnoDB;

 

Now make a copy of the above set and paste it into SQL, then 'Execute', and the conversion is done !

 

Recommended and priority :

If you are using MariaDB like YOORshop, we suggest to convert the MyISAM tables from a database to ARIA (this format is anti-crash, and more powerful) :

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=ARIA;') 

FROM INFORMATION_SCHEMA.TABLES

WHERE ENGINE='MyISAM'

 

To convert InnoDB tables from a database to ARIA :

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=ARIA;') 

FROM INFORMATION_SCHEMA.TABLES

WHERE ENGINE='InnoDB'

 

To convert ARIA tables from a database to InnoDB :

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') 

FROM INFORMATION_SCHEMA.TABLES

WHERE ENGINE='ARIA'

 

If your database looks big with no reason, we know for prestashop that you can empty these tables without issues, button 'empty' :

ps_connections

ps_connections_page

ps_connections_source

ps_guest (this one is linked to stats modules enabled in prestashop, and it will grow again if you don't disable it)

ps_pagenotfound

ps_page_viewed

ps_referrer_cache

ps_date_range

 

ps_search_index + ps_search_word + ps_statssearch must be done at same time, and you must rebuild search index after in prestashop

Other tables related to modules can also cause big table size, storing cache, filter by phpmyadmin to check this, and contact editor to get advices 

 

NB : To migrate to another mysql server, you must convert the aria table back to innodb or myslam 

 

Export, options:

Uncheck AUTO_INCREMENT

Edit database with notepad+, and remove everywhere TRANSACTIONAL = 1

Import database, there should not be any errors (format MYSQL 40 can help)