I have an online store with approximately 30,000 products in the database. The website is currently experiencing significant slow loading times, taking around 5 to 10 minutes to fully load. We need an expert to optimize the performance and speed of our web shop.

Current Setup
– – – – – – – – – – –
– The homepage displays around 30 products.
– We aim to showcase the most recent products added to our site.

Identified Issues:
– – – – – – – – – – – –
1️⃣ Database Size
– Our database contains 30,000 products across various categories.
– Many of these categories are not required for our current operations.
– Reducing unnecessary categories will decrease the database size and improve performance.

2️⃣ Unoptimized Query

– The current query is inefficient, as it searches through all 30,000 products to display just 30 on the index page.
– This extensive search process causes significant delays.
👉By creating a separate table for recently added products, you significantly reduce the amount of data that needs to be queried, thereby improving the performance and speed of your web shop.

Sample Query:
The following is an example of one of the queries used, which takes around 33 seconds to execute:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1
AND ( wp_posts.ID NOT IN (
  SELECT object_id
  FROM wp_term_relationships
  WHERE term_taxonomy_id IN (13,15)
))
AND ( ( wp_postmeta.meta_key = ‘_stock_status’
  AND wp_postmeta.meta_value != ‘outofstock’
))
AND ((wp_posts.post_type = ‘product’
  AND (wp_posts.post_status = ‘publish’
  OR wp_posts.post_status = ‘acf-disabled’
  OR wp_posts.post_status = ‘wc-bestalld’
  OR wp_posts.post_status = ‘private’)
))
GROUP BY wp_posts.ID
ORDER BY wp_posts.menu_order ASC, wp_posts.post_title ASC
LIMIT 0, 16;

Responsibilities:
– – – – – – – – – – – –
– Optimize the database by removing unnecessary product categories.
– Improve the efficiency of database queries.
– Ensure that the homepage loads quickly by optimizing how the most recent products are displayed.
– Implement best practices for database indexing and query optimization.

How to Apply
– – – – – – – – – –
Please provide examples of similar projects you have worked on, along with a brief description of the challenges faced and how you addressed them. Include your proposed approach to optimize our website’s performance.

Staging website
– – – – – – – – – – – –
We have prepared a staging site with identical code as the live site.
We will give you access to the staging site to check the code.

Posted On: July 10, 2024 22:07 UTC
Category: Database Development
Skills:MySQL, MySQL Programming, WordPress, Database

Country: Sweden

click to apply