Database optimization in Wordpress - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to optimize a WordPress database by cleaning up post revisions.

Wordpress

DELETE FROM wp_posts WHERE post_type = '[1]';

Drag options to blanks, or click blank then click option'

Arevision

Bpage

Cattachment

Dnav_menu_item

Attempts:

3 left

💡 Hint

Common Mistakes

Deleting 'page' post_type instead of 'revision'.

Trying to delete 'attachment' which stores media files.

Post revisions are stored as 'revision' post_type. Deleting them helps optimize the database.

2fill in blank

medium

Complete the code to add an index to the wp_postmeta table for faster queries.

Wordpress

ALTER TABLE wp_postmeta ADD INDEX [1] (meta_key);

Drag options to blanks, or click blank then click option'

Aidx_meta_key

Bmeta_index

Cindex_meta

Dkey_index

Attempts:

3 left

💡 Hint

Common Mistakes

Using invalid index names that don't follow conventions.

Not adding an index at all.

Adding an index named 'idx_meta_key' on meta_key speeds up meta queries.

3fill in blank

hard

Fix the error in the SQL query to optimize the wp_options table by removing expired transients.

Wordpress

DELETE FROM wp_options WHERE option_name LIKE [1] AND option_value < UNIX_TIMESTAMP();

Drag options to blanks, or click blank then click option'

A'transient_%'

B'_transient_timeout_%'

C'_transient_%'

D'timeout_transient_%'

Attempts:

3 left

💡 Hint

Common Mistakes

Using '_transient_%' which matches transient data, not timeouts.

Using wrong wildcard patterns.

Expired transients are stored with option_name starting '_transient_timeout_'.

4fill in blank

hard

Fill both blanks to create a query that optimizes the wp_comments table by removing spam and trash comments.

Wordpress

DELETE FROM wp_comments WHERE comment_approved [1] 'spam' OR comment_approved [2] 'trash';

Drag options to blanks, or click blank then click option'

A=

B!=

CLIKE

DNOT LIKE

Attempts:

3 left

💡 Hint

Common Mistakes

Using '!=' which would delete non-spam comments.

Using LIKE without wildcards.

We delete comments where comment_approved equals 'spam' or 'trash' to clean the table.

5fill in blank

hard

Fill all three blanks to write a query that selects post IDs and titles from wp_posts where the post status is 'publish' and the post type is 'post'.

Wordpress

SELECT [1], [2] FROM wp_posts WHERE post_status = '[3]' AND post_type = 'post';

Drag options to blanks, or click blank then click option'

AID

Bpost_title

Cpublish

Ddraft

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'draft' instead of 'publish' for post_status.

Selecting wrong columns.

Selecting ID and post_title where post_status is 'publish' returns all published posts.

Practice

1. What is the main purpose of database optimization in WordPress?

easy

A. To change the website's theme

B. To add more plugins automatically

C. To make the website load faster and run more efficiently

D. To increase the number of posts displayed

Solution

1. Step 1: Understand database optimization

   Database optimization improves how quickly and efficiently data is accessed and stored.

2. Step 2: Relate to WordPress site performance

   A faster database means the website loads quicker and runs smoother for visitors.

3. Final Answer:

   To make the website load faster and run more efficiently -> Option C

4. Quick Check:

   Database optimization = faster site [OK]

Hint: Optimization improves speed and efficiency [OK]

Common Mistakes:

• Thinking optimization adds features
• Confusing optimization with design changes
• Believing optimization increases content automatically

2. Which of the following is the correct SQL command to optimize a WordPress database table named wp_posts?

easy

A. UPDATE wp_posts SET optimize;

B. OPTIMIZE TABLE wp_posts;

C. SELECT * FROM wp_posts OPTIMIZE;

D. DELETE OPTIMIZE FROM wp_posts;

Solution

1. Step 1: Identify the SQL syntax for optimization

   The correct SQL command to optimize a table is OPTIMIZE TABLE table_name;.

2. Step 2: Match the command to the WordPress table

   For the wp_posts table, the command is OPTIMIZE TABLE wp_posts;.

3. Final Answer:

   OPTIMIZE TABLE wp_posts; -> Option B

4. Quick Check:

   OPTIMIZE TABLE syntax = OPTIMIZE TABLE wp_posts; [OK]

Hint: OPTIMIZE TABLE is the correct SQL command [OK]

Common Mistakes:

• Using UPDATE or DELETE incorrectly
• Placing OPTIMIZE after SELECT
• Missing semicolon at the end

3. Consider this WP-CLI command: wp db optimize. What will be the output after running this command?

medium

A. It will optimize all WordPress database tables and show a success message

B. It will delete all posts from the database

C. It will update WordPress to the latest version

D. It will backup the database automatically

Solution

1. Step 1: Understand the WP-CLI command wp db optimize

   This command optimizes all tables in the WordPress database to improve performance.

2. Step 2: Identify the expected output

   After running, it shows a success message confirming optimization.

3. Final Answer:

   It will optimize all WordPress database tables and show a success message -> Option A

4. Quick Check:

   wp db optimize = optimize tables + success message [OK]

Hint: wp db optimize optimizes all tables [OK]

Common Mistakes:

• Confusing optimization with backup
• Thinking it deletes content
• Assuming it updates WordPress core

4. You run the SQL command OPTIMIZE wp_options; but get an error. What is the likely problem?

medium

A. The command is missing the word TABLE after OPTIMIZE

B. The table wp_options does not exist

C. OPTIMIZE cannot be run on WordPress tables

D. You need to use DELETE instead of OPTIMIZE

Solution

1. Step 1: Check SQL syntax for OPTIMIZE command

   The correct syntax requires the keyword TABLE: OPTIMIZE TABLE table_name;.

2. Step 2: Identify the missing keyword in the command

   The command OPTIMIZE wp_options; misses TABLE, causing an error.

3. Final Answer:

   The command is missing the word TABLE after OPTIMIZE -> Option A

4. Quick Check:

   OPTIMIZE TABLE required, missing TABLE causes error [OK]

Hint: Always use OPTIMIZE TABLE, not just OPTIMIZE [OK]

Common Mistakes:

• Omitting TABLE keyword
• Assuming table doesn't exist without checking
• Using DELETE instead of OPTIMIZE

5. You want to optimize your WordPress database but keep a backup first. Which sequence of commands is best to safely optimize the database?

hard

A. wp db import backup.sql && wp db optimize

B. wp db optimize && wp db export backup.sql

C. wp db reset && wp db optimize

D. wp db export backup.sql && wp db optimize

Solution

1. Step 1: Understand the need for backup before optimization

   Backing up first ensures you can restore data if optimization causes issues.

2. Step 2: Analyze the command sequence

   wp db export backup.sql creates a backup file, then wp db optimize optimizes the database safely.

3. Step 3: Check other options for correctness

   wp db optimize && wp db export backup.sql optimizes before backup (risky), wp db reset && wp db optimize resets database (dangerous), wp db import backup.sql && wp db optimize imports backup before optimizing (doesn't create a new backup first).

4. Final Answer:

   wp db export backup.sql && wp db optimize -> Option D

5. Quick Check:

   Backup then optimize = safe practice [OK]

Hint: Always backup before optimizing database [OK]

Common Mistakes:

• Optimizing before backup
• Resetting database accidentally
• Importing backup instead of exporting