¿Cómo desfragmentar la base de datos de Prestashop?


14 Agosto 2015

En este post voy a explicar como desfragmentar la base de datos de Prestashop. Es necesario tener acceso a PHPMyAdmin o en mi caso MySql Workbench (es necesario acceso remoto).

Este caso solo sirve para tablas InnoDB, para tablas MyISAM con solo reparar las tablas ya esta, pero con InnoDB una reparación no hace nada.

Por lo tanto para desfragmentar InnoDB basta con hacer un ALTER TABLE por cada tabla.

Les recomiendo hacer un backup antes que nada y en caso de no saber usar PHPMyAdmin que ni lo intenten. Si bien es algo simple, un error y puede dejar la base de datos inservible.

¿Cómo lo hacemos para todas las tablas de la base de datos? Vamos a PHPMyAdmin, seleccionamos la base de datos en cuestión, solapa SQL y ponemos esto (reemplazando NOMBRE_DE_TU_DB por el nombre de tu base de datos):

 

SELECT 
    concat('ALTER TABLE ', TABLE_NAME, ' ENGINE=innodb;')
FROM
    INFORMATION_SCHEMA.TABLES
WHERE ENGINE = 'innodb' AND table_schema = 'NOMBRE_DE_TU_DB'
LIMIT 0 , 300


Por ejemplo con una base de datos llamada presta_db quedaria:

SELECT 
    concat('ALTER TABLE ', TABLE_NAME, ' ENGINE=innodb;')
FROM
    INFORMATION_SCHEMA.TABLES
WHERE ENGINE = 'innodb' AND table_schema = 'presta_db' 
LIMIT 0 , 300


Una vez hecha esta consulta apretamos en +opciones y marcamos "textos completos" para que nos muestre los ALTER TABLE completos (sino los resume poniendo puntos suspensivos (...) al final y no nos sirve).

Seleciconamos todos los ALTER TABLE desde el primero hasta el final quedando algo asi (en mi base de datos):

ALTER TABLE ps_access ENGINE=innodb;
ALTER TABLE ps_accessory ENGINE=innodb;
ALTER TABLE ps_address ENGINE=innodb;
ALTER TABLE ps_address_format ENGINE=innodb;
ALTER TABLE ps_alias ENGINE=innodb;
ALTER TABLE ps_attachment ENGINE=innodb;
ALTER TABLE ps_attachment_lang ENGINE=innodb;
ALTER TABLE ps_attribute ENGINE=innodb;
ALTER TABLE ps_attribute_group ENGINE=innodb;
ALTER TABLE ps_attribute_group_lang ENGINE=innodb;
ALTER TABLE ps_attribute_group_shop ENGINE=innodb;
ALTER TABLE ps_attribute_impact ENGINE=innodb;
ALTER TABLE ps_attribute_lang ENGINE=innodb;
ALTER TABLE ps_attribute_shop ENGINE=innodb;
ALTER TABLE ps_blocklinkfooter ENGINE=innodb;
ALTER TABLE ps_blocklinkfooter_lang ENGINE=innodb;
ALTER TABLE ps_blocklinkfooter_shop ENGINE=innodb;
ALTER TABLE ps_carrier ENGINE=innodb;
ALTER TABLE ps_carrier_group ENGINE=innodb;
ALTER TABLE ps_carrier_lang ENGINE=innodb;
ALTER TABLE ps_carrier_shop ENGINE=innodb;
ALTER TABLE ps_carrier_tax_rules_group_shop ENGINE=innodb;
ALTER TABLE ps_carrier_zone ENGINE=innodb;
ALTER TABLE ps_cart ENGINE=innodb;
ALTER TABLE ps_cart_cart_rule ENGINE=innodb;
ALTER TABLE ps_cart_product ENGINE=innodb;
ALTER TABLE ps_cart_rule ENGINE=innodb;
ALTER TABLE ps_cart_rule_carrier ENGINE=innodb;
ALTER TABLE ps_cart_rule_combination ENGINE=innodb;
ALTER TABLE ps_cart_rule_country ENGINE=innodb;
ALTER TABLE ps_cart_rule_group ENGINE=innodb;
ALTER TABLE ps_cart_rule_lang ENGINE=innodb;
ALTER TABLE ps_cart_rule_product_rule ENGINE=innodb;
ALTER TABLE ps_cart_rule_product_rule_group ENGINE=innodb;
ALTER TABLE ps_cart_rule_product_rule_value ENGINE=innodb;
ALTER TABLE ps_cart_rule_shop ENGINE=innodb;
ALTER TABLE ps_category ENGINE=innodb;
ALTER TABLE ps_category_group ENGINE=innodb;
ALTER TABLE ps_category_lang ENGINE=innodb;
ALTER TABLE ps_category_product ENGINE=innodb;
ALTER TABLE ps_category_shop ENGINE=innodb;
ALTER TABLE ps_cms ENGINE=innodb;
ALTER TABLE ps_cms_block ENGINE=innodb;
ALTER TABLE ps_cms_block_lang ENGINE=innodb;
ALTER TABLE ps_cms_block_page ENGINE=innodb;
ALTER TABLE ps_cms_block_shop ENGINE=innodb;
ALTER TABLE ps_cms_category ENGINE=innodb;
ALTER TABLE ps_cms_category_lang ENGINE=innodb;
ALTER TABLE ps_cms_lang ENGINE=innodb;
ALTER TABLE ps_cms_shop ENGINE=innodb;
ALTER TABLE ps_compare ENGINE=innodb;
ALTER TABLE ps_compare_product ENGINE=innodb;
ALTER TABLE ps_configuration ENGINE=innodb;
ALTER TABLE ps_configuration_lang ENGINE=innodb;
ALTER TABLE ps_connections ENGINE=innodb;
ALTER TABLE ps_connections_page ENGINE=innodb;
ALTER TABLE ps_connections_source ENGINE=innodb;
ALTER TABLE ps_contact ENGINE=innodb;
ALTER TABLE ps_contact_lang ENGINE=innodb;
ALTER TABLE ps_contact_shop ENGINE=innodb;
ALTER TABLE ps_correoandreani_orders ENGINE=innodb;
ALTER TABLE ps_correoandreani_orders_details ENGINE=innodb;
ALTER TABLE ps_country ENGINE=innodb;
ALTER TABLE ps_country_lang ENGINE=innodb;
ALTER TABLE ps_country_shop ENGINE=innodb;
ALTER TABLE ps_currency ENGINE=innodb;
ALTER TABLE ps_currency_shop ENGINE=innodb;
ALTER TABLE ps_customer ENGINE=innodb;
ALTER TABLE ps_customer_group ENGINE=innodb;
ALTER TABLE ps_customer_message ENGINE=innodb;
ALTER TABLE ps_customer_message_sync_imap ENGINE=innodb;
ALTER TABLE ps_customer_profile_connect ENGINE=innodb;
ALTER TABLE ps_customer_thread ENGINE=innodb;
ALTER TABLE ps_customization ENGINE=innodb;
ALTER TABLE ps_customization_field ENGINE=innodb;
ALTER TABLE ps_customization_field_lang ENGINE=innodb;
ALTER TABLE ps_customized_data ENGINE=innodb;
ALTER TABLE ps_customlink ENGINE=innodb;
ALTER TABLE ps_customlink_lang ENGINE=innodb;
ALTER TABLE ps_customlink_shop ENGINE=innodb;
ALTER TABLE ps_date_range ENGINE=innodb;
ALTER TABLE ps_delivery ENGINE=innodb;
ALTER TABLE ps_employee ENGINE=innodb;
ALTER TABLE ps_employee_shop ENGINE=innodb;
ALTER TABLE ps_favorite_product ENGINE=innodb;
ALTER TABLE ps_feature ENGINE=innodb;
ALTER TABLE ps_feature_lang ENGINE=innodb;
ALTER TABLE ps_feature_product ENGINE=innodb;
ALTER TABLE ps_feature_shop ENGINE=innodb;
ALTER TABLE ps_feature_value ENGINE=innodb;
ALTER TABLE ps_feature_value_lang ENGINE=innodb;
ALTER TABLE ps_gender ENGINE=innodb;
ALTER TABLE ps_gender_lang ENGINE=innodb;
ALTER TABLE ps_group ENGINE=innodb;
ALTER TABLE ps_group_lang ENGINE=innodb;
ALTER TABLE ps_group_reduction ENGINE=innodb;
ALTER TABLE ps_group_shop ENGINE=innodb;
ALTER TABLE ps_guest ENGINE=innodb;
ALTER TABLE ps_help_access ENGINE=innodb;
ALTER TABLE ps_hook ENGINE=innodb;
ALTER TABLE ps_hook_alias ENGINE=innodb;
ALTER TABLE ps_hook_module ENGINE=innodb;
ALTER TABLE ps_hook_module_exceptions ENGINE=innodb;
ALTER TABLE ps_image ENGINE=innodb;
ALTER TABLE ps_image_lang ENGINE=innodb;
ALTER TABLE ps_image_shop ENGINE=innodb;
ALTER TABLE ps_image_type ENGINE=innodb;
ALTER TABLE ps_import_match ENGINE=innodb;
ALTER TABLE ps_lang ENGINE=innodb;
ALTER TABLE ps_lang_shop ENGINE=innodb;
ALTER TABLE ps_layered_category ENGINE=innodb;
ALTER TABLE ps_layered_filter ENGINE=innodb;
ALTER TABLE ps_layered_filter_shop ENGINE=innodb;
ALTER TABLE ps_layered_friendly_url ENGINE=innodb;
ALTER TABLE ps_layered_indexable_attribute_group ENGINE=innodb;
ALTER TABLE ps_layered_indexable_attribute_group_lang_value ENGINE=innodb;
ALTER TABLE ps_layered_indexable_attribute_lang_value ENGINE=innodb;
ALTER TABLE ps_layered_indexable_feature ENGINE=innodb;
ALTER TABLE ps_layered_indexable_feature_lang_value ENGINE=innodb;
ALTER TABLE ps_layered_indexable_feature_value_lang_value ENGINE=innodb;
ALTER TABLE ps_layered_price_index ENGINE=innodb;
ALTER TABLE ps_layered_product_attribute ENGINE=innodb;
ALTER TABLE ps_log ENGINE=innodb;
ALTER TABLE ps_log_email ENGINE=innodb;
ALTER TABLE ps_mailalert_customer_oos ENGINE=innodb;
ALTER TABLE ps_manufacturer ENGINE=innodb;
ALTER TABLE ps_manufacturer_lang ENGINE=innodb;
ALTER TABLE ps_manufacturer_shop ENGINE=innodb;
ALTER TABLE ps_memcached_servers ENGINE=innodb;
ALTER TABLE ps_message ENGINE=innodb;
ALTER TABLE ps_message_readed ENGINE=innodb;
ALTER TABLE ps_meta ENGINE=innodb;
ALTER TABLE ps_meta_lang ENGINE=innodb;
ALTER TABLE ps_minic_options_adv ENGINE=innodb;
ALTER TABLE ps_minic_slider_adv ENGINE=innodb;
ALTER TABLE ps_module ENGINE=innodb;
ALTER TABLE ps_module_access ENGINE=innodb;
ALTER TABLE ps_module_country ENGINE=innodb;
ALTER TABLE ps_module_currency ENGINE=innodb;
ALTER TABLE ps_module_group ENGINE=innodb;
ALTER TABLE ps_module_preference ENGINE=innodb;
ALTER TABLE ps_module_shop ENGINE=innodb;
ALTER TABLE ps_operating_system ENGINE=innodb;
ALTER TABLE ps_order_carrier ENGINE=innodb;
ALTER TABLE ps_order_cart_rule ENGINE=innodb;
ALTER TABLE ps_order_detail ENGINE=innodb;
ALTER TABLE ps_order_detail_tax ENGINE=innodb;
ALTER TABLE ps_order_history ENGINE=innodb;
ALTER TABLE ps_order_invoice ENGINE=innodb;
ALTER TABLE ps_order_invoice_payment ENGINE=innodb;
ALTER TABLE ps_order_invoice_tax ENGINE=innodb;
ALTER TABLE ps_order_message ENGINE=innodb;
ALTER TABLE ps_order_message_lang ENGINE=innodb;
ALTER TABLE ps_order_payment ENGINE=innodb;
ALTER TABLE ps_order_return ENGINE=innodb;
ALTER TABLE ps_order_return_detail ENGINE=innodb;
ALTER TABLE ps_order_return_state ENGINE=innodb;
ALTER TABLE ps_order_return_state_lang ENGINE=innodb;
ALTER TABLE ps_order_slip ENGINE=innodb;
ALTER TABLE ps_order_slip_detail ENGINE=innodb;
ALTER TABLE ps_order_state ENGINE=innodb;
ALTER TABLE ps_order_state_lang ENGINE=innodb;
ALTER TABLE ps_orders ENGINE=innodb;
ALTER TABLE ps_pack ENGINE=innodb;
ALTER TABLE ps_page ENGINE=innodb;
ALTER TABLE ps_page_type ENGINE=innodb;
ALTER TABLE ps_page_viewed ENGINE=innodb;
ALTER TABLE ps_pk_awShowcaseSlider ENGINE=innodb;
ALTER TABLE ps_pk_awShowcaseSlider_options ENGINE=innodb;
ALTER TABLE ps_pk_awShowcaseSlider_points ENGINE=innodb;
ALTER TABLE ps_pk_bxslider ENGINE=innodb;
ALTER TABLE ps_pk_bxslider_options ENGINE=innodb;
ALTER TABLE ps_pk_flexmenu_links ENGINE=innodb;
ALTER TABLE ps_pk_flexmenu_links_lang ENGINE=innodb;
ALTER TABLE ps_pk_flexmenu_menus ENGINE=innodb;
ALTER TABLE ps_pk_flexmenu_menus_lang ENGINE=innodb;
ALTER TABLE ps_pk_flexmenu_submenus ENGINE=innodb;
ALTER TABLE ps_pk_product_extratabs ENGINE=innodb;
ALTER TABLE ps_pk_theme_settings ENGINE=innodb;
ALTER TABLE ps_prestafraud_carrier ENGINE=innodb;
ALTER TABLE ps_prestafraud_carts ENGINE=innodb;
ALTER TABLE ps_prestafraud_orders ENGINE=innodb;
ALTER TABLE ps_prestafraud_payment ENGINE=innodb;
ALTER TABLE ps_product ENGINE=innodb;
ALTER TABLE ps_product_attachment ENGINE=innodb;
ALTER TABLE ps_product_attribute ENGINE=innodb;
ALTER TABLE ps_product_attribute_combination ENGINE=innodb;
ALTER TABLE ps_product_attribute_image ENGINE=innodb;
ALTER TABLE ps_product_attribute_shop ENGINE=innodb;
ALTER TABLE ps_product_carrier ENGINE=innodb;
ALTER TABLE ps_product_country_tax ENGINE=innodb;
ALTER TABLE ps_product_download ENGINE=innodb;
ALTER TABLE ps_product_group_reduction_cache ENGINE=innodb;
ALTER TABLE ps_product_lang ENGINE=innodb;
ALTER TABLE ps_product_sale ENGINE=innodb;
ALTER TABLE ps_product_shop ENGINE=innodb;
ALTER TABLE ps_product_supplier ENGINE=innodb;
ALTER TABLE ps_product_tag ENGINE=innodb;
ALTER TABLE ps_profile ENGINE=innodb;
ALTER TABLE ps_profile_lang ENGINE=innodb;
ALTER TABLE ps_promokit_isotop ENGINE=innodb;
ALTER TABLE ps_quick_access ENGINE=innodb;
ALTER TABLE ps_quick_access_lang ENGINE=innodb;
ALTER TABLE ps_range_price ENGINE=innodb;
ALTER TABLE ps_range_weight ENGINE=innodb;
ALTER TABLE ps_referrer ENGINE=innodb;
ALTER TABLE ps_referrer_cache ENGINE=innodb;
ALTER TABLE ps_referrer_shop ENGINE=innodb;
ALTER TABLE ps_request_sql ENGINE=innodb;
ALTER TABLE ps_required_field ENGINE=innodb;
ALTER TABLE ps_risk ENGINE=innodb;
ALTER TABLE ps_risk_lang ENGINE=innodb;
ALTER TABLE ps_scene ENGINE=innodb;
ALTER TABLE ps_scene_category ENGINE=innodb;
ALTER TABLE ps_scene_lang ENGINE=innodb;
ALTER TABLE ps_scene_products ENGINE=innodb;
ALTER TABLE ps_scene_shop ENGINE=innodb;
ALTER TABLE ps_search_engine ENGINE=innodb;
ALTER TABLE ps_search_index ENGINE=innodb;
ALTER TABLE ps_search_word ENGINE=innodb;
ALTER TABLE ps_shop ENGINE=innodb;
ALTER TABLE ps_shop_group ENGINE=innodb;
ALTER TABLE ps_shop_url ENGINE=innodb;
ALTER TABLE ps_smart_blog_category ENGINE=innodb;
ALTER TABLE ps_smart_blog_category_lang ENGINE=innodb;
ALTER TABLE ps_smart_blog_category_shop ENGINE=innodb;
ALTER TABLE ps_smart_blog_comment ENGINE=innodb;
ALTER TABLE ps_smart_blog_comment_shop ENGINE=innodb;
ALTER TABLE ps_smart_blog_imagetype ENGINE=innodb;
ALTER TABLE ps_smart_blog_media ENGINE=innodb;
ALTER TABLE ps_smart_blog_post ENGINE=innodb;
ALTER TABLE ps_smart_blog_post_category ENGINE=innodb;
ALTER TABLE ps_smart_blog_post_lang ENGINE=innodb;
ALTER TABLE ps_smart_blog_post_related ENGINE=innodb;
ALTER TABLE ps_smart_blog_post_shop ENGINE=innodb;
ALTER TABLE ps_smart_blog_post_tag ENGINE=innodb;
ALTER TABLE ps_smart_blog_product_related ENGINE=innodb;
ALTER TABLE ps_smart_blog_tag ENGINE=innodb;
ALTER TABLE ps_specific_price ENGINE=innodb;
ALTER TABLE ps_specific_price_priority ENGINE=innodb;
ALTER TABLE ps_specific_price_rule ENGINE=innodb;
ALTER TABLE ps_specific_price_rule_condition ENGINE=innodb;
ALTER TABLE ps_specific_price_rule_condition_group ENGINE=innodb;
ALTER TABLE ps_state ENGINE=innodb;
ALTER TABLE ps_statssearch ENGINE=innodb;
ALTER TABLE ps_stock ENGINE=innodb;
ALTER TABLE ps_stock_available ENGINE=innodb;
ALTER TABLE ps_stock_mvt ENGINE=innodb;
ALTER TABLE ps_stock_mvt_reason ENGINE=innodb;
ALTER TABLE ps_stock_mvt_reason_lang ENGINE=innodb;
ALTER TABLE ps_store ENGINE=innodb;
ALTER TABLE ps_store_shop ENGINE=innodb;
ALTER TABLE ps_supplier ENGINE=innodb;
ALTER TABLE ps_supplier_lang ENGINE=innodb;
ALTER TABLE ps_supplier_shop ENGINE=innodb;
ALTER TABLE ps_supply_order ENGINE=innodb;
ALTER TABLE ps_supply_order_detail ENGINE=innodb;
ALTER TABLE ps_supply_order_history ENGINE=innodb;
ALTER TABLE ps_supply_order_receipt_history ENGINE=innodb;
ALTER TABLE ps_supply_order_state ENGINE=innodb;
ALTER TABLE ps_supply_order_state_lang ENGINE=innodb;
ALTER TABLE ps_tab ENGINE=innodb;
ALTER TABLE ps_tab_lang ENGINE=innodb;
ALTER TABLE ps_tab_module_preference ENGINE=innodb;
ALTER TABLE ps_tag ENGINE=innodb;
ALTER TABLE ps_tax ENGINE=innodb;
ALTER TABLE ps_tax_lang ENGINE=innodb;
ALTER TABLE ps_tax_rule ENGINE=innodb;
ALTER TABLE ps_tax_rules_group ENGINE=innodb;
ALTER TABLE ps_tax_rules_group_shop ENGINE=innodb;
ALTER TABLE ps_theme ENGINE=innodb;
ALTER TABLE ps_theme_specific ENGINE=innodb;
ALTER TABLE ps_timezone ENGINE=innodb;
ALTER TABLE ps_warehouse ENGINE=innodb;
ALTER TABLE ps_warehouse_carrier ENGINE=innodb;
ALTER TABLE ps_warehouse_product_location ENGINE=innodb;
ALTER TABLE ps_warehouse_shop ENGINE=innodb;
ALTER TABLE ps_web_browser ENGINE=innodb;
ALTER TABLE ps_webservice_account ENGINE=innodb;
ALTER TABLE ps_webservice_account_shop ENGINE=innodb;
ALTER TABLE ps_webservice_permission ENGINE=innodb;
ALTER TABLE ps_zone ENGINE=innodb;
ALTER TABLE ps_zone_shop ENGINE=innodb;

Esta consulta es la que tenemos que ejecutar para desfragmentar nuestra base de datos. Si ejecutan esta consulta seguramente les de error porque yo tengo tablas que pertenecen a modulos que instalé yo. Cada base de datos de Prestashop difiere, por eso sigan estos pasos para obtener la consulta final perteneciente a su base de datos.

Parece algo engorroso, pero no lo es. Es sencillo una vez que tienen la consulta final.

Con esta última consulta van a SQL, pegan su consulta, la ejecutan y esperan. Puede tardar unos 5 o 10 minutos dependiendo de cuan pesada sea su base de datos.

Espero que les haya servido, cualquier duda comenten y los ayudo.

Más artículos


Soy Desarrollador Mercado Pago Certificado

Soy Desarrollador Mercado Pago Certificado

02 Octubre 2020
Para Online Payments Checkout Pro. Esta semana participé del "Mercado Pago Developer Partner Program" y luego de realizar el examen correspondiente fuí aprobado para ser "Mercado Pago Certified Developer for Online Payments ...

Calculadora Compuesta de Cauciones - Proyecto personal

17 Marzo 2020
Bueno, encaré este proyecto viendo que IOL (InvertirOnline) daba un excel para el cálculo estimativo de las ganancias para las cauciones y siempre por mi cuenta calculaba las ganancias haciendo interés compuesto. Por ejemplo s...

Libreria PHP para Andreani REST

24 Agosto 2019
Qué es esto? Es una librería php para conectar con la Api REST de Andreani. Creé la librería porque me ví casi forzado a migrar a la Api REST de Andreani. Hice un módulo en su momento, y sigo manteniendo, para Prestashop de And...

Liberar memoria ram en servidor CentOS

01 Agosto 2019
Cómo hacemos para liberar la memoria ram de nuestro servidor CentOS? Bueno, entramos por ssh al servidor y luego ejecutamos los siguientes comandos: free -m sudo sync && sudo sysctl -w vm.drop_caches=3 free -m ...

Agregar reCaptcha en Joomla 1.5

03 Abril 2019
Para agregar reCaptcha al contacto en Joomla 1.5 (SOLO J1.5) instalamos el plugin reCAPTCHAIntegrator y luego copiamos el archivo /components/com_contact/views/contact/tmpl/default_form.php a /templates/NOMBRE_TEMPLATE/html/com...

Configurar DKIM con Exim en Centos 7

13 Febrero 2019
Vamos a configurar DKIM con Exim en CentOS 7. Primero vamos a generar las claves privada y pública con openssl, sin utilizar opendkim ni nada parecido. Conectamos vía SSH al servidor en cuestión y ejecutamos: openssl genrs...