Blog

Pods Data Storage vs CPT Custom Fields – MySQL Testing

Comparing Data Storage Models on the MySQL level

This is a specific comparison between Pods 2.0 and CPT on the MySQL side of things, there are a couple of minor differences data-wise with Pods as it is now, but it’s roughly the same figure.

Here’s the setup — so we’ve got 1 content type called ‘test’. It has 20 custom fields. Each field is filled with randomized data (for real world probability of contents). For our testing purposes here, we are strictly comparing the Custom Field model WP uses vs the Pods Custom Table model for data storage. As such, the test has been simplified to target the differences between the two models.

I setup three tables: test_data (the main fields), test_meta (custom fields storage example), and test_tbl (pods storage example). You can get the Table SQL downloading this file: test.zip (contains test.sql)

I then inserted 50,000 ‘items’ into the test data type. I did this using this script: test-insert.php.txt (remove .txt to use, also I did this in phases, depending on your server you may need to modify the script $x end value until you’ve got your ideal amount of data inserted)

Pods vs CPT Custom Fields – a MySQL Test

(list shown in all cases with Pods first and CPT Custom Fields second)

  • The Basics
    • Empty Table Index Length (bytes):
      • 1024 vs 4096 (Pods is 4x less)
    • Table Columns:
      • 21 vs 4 (Pods stores each custom field as a column in it’s object type’s table)
    • SQL JOINs Required:
      • 1 vs 20 (Pods is 20x less)
  • With 50,000 ‘test’ items added w/ 20 additional non-main fields (Custom Fields)
    • Index Length (bytes):
      • 716,800 vs 31,690,752 (Pods is 20x less)
    • Data Length(bytes):
      • 32,400,000 vs 72,800,000 (Pods is over 2x less)
    • Rows:
      • 50,000 vs 1,000,000 (Pods is 20x less)
  • MySQL Testing
    • Test One: Listing using Relational Method – Select all Custom Field data from 20 rows
      • Query Time (seconds):
        • 0.010 vs 0.127 (Pods is more than 10x less)
      • Queries Used
        • Pods Query
          • SELECT p.*, t.*
            FROM test_data AS p
            LEFT JOIN test_tbl AS t ON t.id = p.id
            LIMIT 0,20
        • CPT Custom Fields Query
          • SELECT p.*,
            t1.meta_value AS custom_field_1,
            t2.meta_value AS custom_field_2,
            t3.meta_value AS custom_field_3,
            t4.meta_value AS custom_field_4,
            t5.meta_value AS custom_field_5,
            t6.meta_value AS custom_field_6,
            t7.meta_value AS custom_field_7,
            t8.meta_value AS custom_field_8,
            t9.meta_value AS custom_field_9,
            t10.meta_value AS custom_field_10,
            t11.meta_value AS custom_field_11,
            t12.meta_value AS custom_field_12,
            t13.meta_value AS custom_field_13,
            t14.meta_value AS custom_field_14,
            t15.meta_value AS custom_field_15,
            t16.meta_value AS custom_field_16,
            t17.meta_value AS custom_field_17,
            t18.meta_value AS custom_field_18,
            t19.meta_value AS custom_field_19,
            t20.meta_value AS custom_field_20
            FROM test_data AS p
            LEFT JOIN test_meta AS t1 ON t1.related_id = p.id AND t1.meta_key = 'custom field 1'
            LEFT JOIN test_meta AS t2 ON t2.related_id = p.id AND t2.meta_key = 'custom field 2'
            LEFT JOIN test_meta AS t3 ON t3.related_id = p.id AND t3.meta_key = 'custom field 3'
            LEFT JOIN test_meta AS t4 ON t4.related_id = p.id AND t4.meta_key = 'custom field 4'
            LEFT JOIN test_meta AS t5 ON t5.related_id = p.id AND t5.meta_key = 'custom field 5'
            LEFT JOIN test_meta AS t6 ON t6.related_id = p.id AND t6.meta_key = 'custom field 6'
            LEFT JOIN test_meta AS t7 ON t7.related_id = p.id AND t7.meta_key = 'custom field 7'
            LEFT JOIN test_meta AS t8 ON t8.related_id = p.id AND t8.meta_key = 'custom field 8'
            LEFT JOIN test_meta AS t9 ON t9.related_id = p.id AND t9.meta_key = 'custom field 9'
            LEFT JOIN test_meta AS t10 ON t10.related_id = p.id AND t10.meta_key = 'custom field 10'
            LEFT JOIN test_meta AS t11 ON t11.related_id = p.id AND t11.meta_key = 'custom field 11'
            LEFT JOIN test_meta AS t12 ON t12.related_id = p.id AND t12.meta_key = 'custom field 12'
            LEFT JOIN test_meta AS t13 ON t13.related_id = p.id AND t13.meta_key = 'custom field 13'
            LEFT JOIN test_meta AS t14 ON t14.related_id = p.id AND t14.meta_key = 'custom field 14'
            LEFT JOIN test_meta AS t15 ON t15.related_id = p.id AND t15.meta_key = 'custom field 15'
            LEFT JOIN test_meta AS t16 ON t16.related_id = p.id AND t16.meta_key = 'custom field 16'
            LEFT JOIN test_meta AS t17 ON t17.related_id = p.id AND t17.meta_key = 'custom field 17'
            LEFT JOIN test_meta AS t18 ON t18.related_id = p.id AND t18.meta_key = 'custom field 18'
            LEFT JOIN test_meta AS t19 ON t19.related_id = p.id AND t19.meta_key = 'custom field 19'
            LEFT JOIN test_meta AS t20 ON t20.related_id = p.id AND t20.meta_key = 'custom field 20'
            LIMIT 0,20
    • Test Two: Searching AND Listing using Relational Method – Select all Custom Field data from 20 rows with Custom Field 19 containing ’59’
      • Query Time (seconds):
        • 0.013 vs 0.137 (Pods is 10x less)
      • Queries Used
        • Pods Query
          • SELECT p.*, t.*
            FROM test_data AS p
            LEFT JOIN test_tbl AS t ON t.id = p.id
            WHERE t.custom_field_19 LIKE '%59%'
            LIMIT 0,20
        • CPT Custom Fields Query
          • SELECT p.*,
            t1.meta_value AS custom_field_1,
            t2.meta_value AS custom_field_2,
            t3.meta_value AS custom_field_3,
            t4.meta_value AS custom_field_4,
            t5.meta_value AS custom_field_5,
            t6.meta_value AS custom_field_6,
            t7.meta_value AS custom_field_7,
            t8.meta_value AS custom_field_8,
            t9.meta_value AS custom_field_9,
            t10.meta_value AS custom_field_10,
            t11.meta_value AS custom_field_11,
            t12.meta_value AS custom_field_12,
            t13.meta_value AS custom_field_13,
            t14.meta_value AS custom_field_14,
            t15.meta_value AS custom_field_15,
            t16.meta_value AS custom_field_16,
            t17.meta_value AS custom_field_17,
            t18.meta_value AS custom_field_18,
            t19.meta_value AS custom_field_19,
            t20.meta_value AS custom_field_20
            FROM test_data AS p
            LEFT JOIN test_meta AS t1 ON t1.related_id = p.id AND t1.meta_key = 'custom field 1'
            LEFT JOIN test_meta AS t2 ON t2.related_id = p.id AND t2.meta_key = 'custom field 2'
            LEFT JOIN test_meta AS t3 ON t3.related_id = p.id AND t3.meta_key = 'custom field 3'
            LEFT JOIN test_meta AS t4 ON t4.related_id = p.id AND t4.meta_key = 'custom field 4'
            LEFT JOIN test_meta AS t5 ON t5.related_id = p.id AND t5.meta_key = 'custom field 5'
            LEFT JOIN test_meta AS t6 ON t6.related_id = p.id AND t6.meta_key = 'custom field 6'
            LEFT JOIN test_meta AS t7 ON t7.related_id = p.id AND t7.meta_key = 'custom field 7'
            LEFT JOIN test_meta AS t8 ON t8.related_id = p.id AND t8.meta_key = 'custom field 8'
            LEFT JOIN test_meta AS t9 ON t9.related_id = p.id AND t9.meta_key = 'custom field 9'
            LEFT JOIN test_meta AS t10 ON t10.related_id = p.id AND t10.meta_key = 'custom field 10'
            LEFT JOIN test_meta AS t11 ON t11.related_id = p.id AND t11.meta_key = 'custom field 11'
            LEFT JOIN test_meta AS t12 ON t12.related_id = p.id AND t12.meta_key = 'custom field 12'
            LEFT JOIN test_meta AS t13 ON t13.related_id = p.id AND t13.meta_key = 'custom field 13'
            LEFT JOIN test_meta AS t14 ON t14.related_id = p.id AND t14.meta_key = 'custom field 14'
            LEFT JOIN test_meta AS t15 ON t15.related_id = p.id AND t15.meta_key = 'custom field 15'
            LEFT JOIN test_meta AS t16 ON t16.related_id = p.id AND t16.meta_key = 'custom field 16'
            LEFT JOIN test_meta AS t17 ON t17.related_id = p.id AND t17.meta_key = 'custom field 17'
            LEFT JOIN test_meta AS t18 ON t18.related_id = p.id AND t18.meta_key = 'custom field 18'
            LEFT JOIN test_meta AS t19 ON t19.related_id = p.id AND t19.meta_key = 'custom field 19'
            LEFT JOIN test_meta AS t20 ON t20.related_id = p.id AND t20.meta_key = 'custom field 20'
            WHERE t19.meta_key = 'custom field 19' AND t19.meta_value LIKE '%59%'
            LIMIT 0,20
    • Test Three: Get All Data using Relational Method – Select all Custom Field data
      • Query Time (seconds):
        • 14.057 vs 282.675 (Pods is over 20x less)
      • Queries Used
        • Pods Query
          • SELECT p.*, t.*
            FROM test_data AS p
            LEFT JOIN test_tbl AS t ON t.id = p.id
        • CPT Custom Fields Query
          • SELECT p.*,
            t1.meta_value AS custom_field_1,
            t2.meta_value AS custom_field_2,
            t3.meta_value AS custom_field_3,
            t4.meta_value AS custom_field_4,
            t5.meta_value AS custom_field_5,
            t6.meta_value AS custom_field_6,
            t7.meta_value AS custom_field_7,
            t8.meta_value AS custom_field_8,
            t9.meta_value AS custom_field_9,
            t10.meta_value AS custom_field_10,
            t11.meta_value AS custom_field_11,
            t12.meta_value AS custom_field_12,
            t13.meta_value AS custom_field_13,
            t14.meta_value AS custom_field_14,
            t15.meta_value AS custom_field_15,
            t16.meta_value AS custom_field_16,
            t17.meta_value AS custom_field_17,
            t18.meta_value AS custom_field_18,
            t19.meta_value AS custom_field_19,
            t20.meta_value AS custom_field_20
            FROM test_data AS p
            LEFT JOIN test_meta AS t1 ON t1.related_id = p.id AND t1.meta_key = 'custom field 1'
            LEFT JOIN test_meta AS t2 ON t2.related_id = p.id AND t2.meta_key = 'custom field 2'
            LEFT JOIN test_meta AS t3 ON t3.related_id = p.id AND t3.meta_key = 'custom field 3'
            LEFT JOIN test_meta AS t4 ON t4.related_id = p.id AND t4.meta_key = 'custom field 4'
            LEFT JOIN test_meta AS t5 ON t5.related_id = p.id AND t5.meta_key = 'custom field 5'
            LEFT JOIN test_meta AS t6 ON t6.related_id = p.id AND t6.meta_key = 'custom field 6'
            LEFT JOIN test_meta AS t7 ON t7.related_id = p.id AND t7.meta_key = 'custom field 7'
            LEFT JOIN test_meta AS t8 ON t8.related_id = p.id AND t8.meta_key = 'custom field 8'
            LEFT JOIN test_meta AS t9 ON t9.related_id = p.id AND t9.meta_key = 'custom field 9'
            LEFT JOIN test_meta AS t10 ON t10.related_id = p.id AND t10.meta_key = 'custom field 10'
            LEFT JOIN test_meta AS t11 ON t11.related_id = p.id AND t11.meta_key = 'custom field 11'
            LEFT JOIN test_meta AS t12 ON t12.related_id = p.id AND t12.meta_key = 'custom field 12'
            LEFT JOIN test_meta AS t13 ON t13.related_id = p.id AND t13.meta_key = 'custom field 13'
            LEFT JOIN test_meta AS t14 ON t14.related_id = p.id AND t14.meta_key = 'custom field 14'
            LEFT JOIN test_meta AS t15 ON t15.related_id = p.id AND t15.meta_key = 'custom field 15'
            LEFT JOIN test_meta AS t16 ON t16.related_id = p.id AND t16.meta_key = 'custom field 16'
            LEFT JOIN test_meta AS t17 ON t17.related_id = p.id AND t17.meta_key = 'custom field 17'
            LEFT JOIN test_meta AS t18 ON t18.related_id = p.id AND t18.meta_key = 'custom field 18'
            LEFT JOIN test_meta AS t19 ON t19.related_id = p.id AND t19.meta_key = 'custom field 19'
            LEFT JOIN test_meta AS t20 ON t20.related_id = p.id AND t20.meta_key = 'custom field 20'
    • Test Four: Test One using Native Method and Full Data – Same as Test One in each table’s native method (Relational vs Key / Value) without the max 20 limit
      • Query Time (seconds):
        • 14.117 vs 80.717 (Pods is nearly 6x less)
      • Queries Used
        • Pods Query (Relational)
          • SELECT p.*, t.*
            FROM test_data AS p
            LEFT JOIN test_tbl AS t ON t.id = p.id
        • CPT Custom Fields Query (Key / Values)
          • SELECT p.*, t.*
            FROM test_data AS p
            LEFT JOIN test_meta AS t ON t.related_id = p.id
    • Test Five: Test Two using Native Method and Full Data – Same as Test Two in each table’s native method (Relational vs Key / Value) without the max 20 limit
      • Query Time (seconds):
        • 2.001 vs 9.591 (Pods is nearly 5x less)
      • Queries Used
        • Pods Query (Relational)
          • SELECT p.id AS main_id, t.*
            FROM test_data AS p
            LEFT JOIN test_tbl AS t ON t.id = p.id
            WHERE t.custom_field_19 LIKE '%59%'
        • CPT Custom Fields Query (Key / Values)
          • SELECT p.*, t.*
            FROM test_data AS p
            LEFT JOIN test_meta AS t ON t.related_id = p.id
            LEFT JOIN test_meta AS t19 ON t19.related_id = p.id AND t19.meta_key = 'custom field 19'
            WHERE t19.meta_key = 'custom field 19' AND t19.meta_value LIKE '%59%'

Remember, this is a ‘perfect world’ strictly MySQL Test. WP and Pods actually use slightly different queries and use PHP to handle a bit of the work too. However, from the data storage model aspect, it’s plain to see that there is a big difference, especially as you go further beyond the 50,000 item mark (easily done with revisions, posts, pages, media, menus, etc.. on a medium or large site). Custom Fields are great for simple stuff, but with content types you need to be sure you’re putting your data in the best place for performance and keep the long-term big picture in mind. This test covers the MySQL aspect of course, there are other areas that I will test in the near future.

Also, this test does not account for Object Caching on the PHP level, which increases performance all around.

2 thoughts on “Pods Data Storage vs CPT Custom Fields – MySQL Testing”

Comments are closed.