Optimmizing wordpress database with milion of rows

  • Posted:
  • Proposals: 20
  • Remote
  • #2019628
  • Open for Proposals
Tega U.Live Deftsoft Informatics P.Vyron D.Rana K.Karanbir S. + 15 others have already sent a proposal.
  • 9


Experience Level: Expert

We are running the shop with spare parts vagport.com on the base of woocommerce. We have more than 7.000 products on stock. We have implemented WPML plugin to have products in 13 languages. We have implemented own php script to have all products translated. And this made a problem. Database has grown to enormous size. Postmeta table has more than 5 million rows. All database is more than 3 GB. To edit product, it takes almost one minute to open edit product page in browser.

After analyzing the SQL query after opening the edit product page from admin panel, e.g.:

SELECT st.status, l.code, st.translator_id, st.translation_service, i.trid
FROM p7zf_icl_languages l
LEFT JOIN p7zf_icl_translations i
ON l.code = i.language_code JOIN p7zf_icl_translation_status st
ON i.translation_id = st.translation_id
WHERE l.active = 1
AND 1=1
OR i.trid IS NULL

takes more than 10 seconds only one query.

After going to higher dedicated server, like: CPU 40 core, 256 GB RAM etc. the problem did not gone.

I am fully aware that for such a big shop implementing woocommerce was not a brilliant idea, but we have a problem now and somehow need to handle it. I wonder of possible solutions:

1. Optimize database? Maybe get rid of some rows. We already get rid of some languages but rows still remain in tables.
2. Implement customized plugin?
3. Go to other solutions?

I kindly ask for help.


Sebastian Radek

New Proposal

Create an account now and send a proposal now to get this job.

Sign up

Clarification Board Ask a Question

  • Naresh S.

    Please let me know if still available?

  • Leandro A.

    Best solution is to move out of WP now otherwise you will run into these problems all the time.