{"id":11605,"date":"2009-06-24T00:09:06","date_gmt":"2009-06-23T22:09:06","guid":{"rendered":"https:\/\/mamchenkov.net\/blog\/?p=93"},"modified":"2009-06-24T00:09:06","modified_gmt":"2009-06-23T22:09:06","slug":"understanding-wordpress-database-in-10-minutes","status":"publish","type":"post","link":"https:\/\/mamchenkov.net\/wordpress\/2009\/06\/24\/understanding-wordpress-database-in-10-minutes\/","title":{"rendered":"Understanding WordPress database in 10 minutes"},"content":{"rendered":"\n

Almost two years ago I wrote a blog post titled “A look inside the WordPress database<\/a>“.\u00a0 While a lot of people enjoyed it (and, apparently still do, even though it’s a bit outdated), I think it could be greatly simplified.\u00a0 And it will probably take you less time to understand WordPress database now than it would take you to read through that blog post back then.<\/p>\n

For the simplified approach to the WordPress database, you’ll need three things and three things only.<\/p>\n

    \n
  1. Database description<\/a> – WordPress Codex page.\u00a0 The main thing to pay attention to on that page is the database diagram.\u00a0 If you can’t grasp it all at once – DON’T PANIC<\/strong> – there is a description of each table further down the same page.\u00a0 But trust me, you don’t need that just yet.<\/li>\n
  2. Fresh installation of WordPress<\/a>.\u00a0 And by fresh I mean the one that you just did, complete with database setup and all, but which you haven’t touched yet – no options changed, no posts or pages published, no comments moderated.\u00a0 Virgin WordPress.<\/li>\n
  3. MySQL client.\u00a0 And you can use whatever suits your fancy.\u00a0 Command line, PHPMyAdmin, MySQL Query Browser, or anything else.\u00a0 The more comfortable you are with it, the better.<\/li>\n<\/ol>\n

    Have you got everything?\u00a0 OK.\u00a0 Now you’ll just need to use that MySQL client to see the tables in your fresh WordPress installation, the structure of those tables, and the content that they have.\u00a0 If your memory fails you, here is a quick guide to MySQL.<\/p>\n

    List all tables in the database:<\/strong><\/p>\n

    mysql> show tables;\n+-----------------------+\n| Tables_in_xxxxxxx_com |\n+-----------------------+\n| wp_comments           |\n| wp_links              |\n| wp_options            |\n| wp_postmeta           |\n| wp_posts              |\n| wp_term_relationships |\n| wp_term_taxonomy      |\n| wp_terms              |\n| wp_usermeta           |\n| wp_users              |\n+-----------------------+\n10 rows in set (0.00 sec)<\/pre>\n
    Show structure of the table<\/strong>:<\/p>\n
    mysql> explain wp_postmeta;\n+------------+---------------------+------+-----+---------+----------------+\n| Field\u00a0\u00a0\u00a0\u00a0\u00a0 | Type\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Null | Key | Default | Extra\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\n+------------+---------------------+------+-----+---------+----------------+\n| meta_id\u00a0\u00a0\u00a0 | bigint(20) unsigned | NO\u00a0\u00a0 | PRI | NULL\u00a0\u00a0\u00a0 | auto_increment |\n| post_id\u00a0\u00a0\u00a0 | bigint(20) unsigned | NO\u00a0\u00a0 | MUL | 0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\n| meta_key\u00a0\u00a0 | varchar(255)\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | YES\u00a0 | MUL | NULL\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\n| meta_value | longtext\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | YES\u00a0 |\u00a0\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\n+------------+---------------------+------+-----+---------+----------------+\n4 rows in set (0.00 sec)<\/pre>\n
    Select all records from the table<\/strong>:<\/p>\n
    mysql> select * from wp_terms;\n+---------+---------------+---------------+------------+\n| term_id | name          | slug          | term_group |\n+---------+---------------+---------------+------------+\n|       1 | Uncategorized | uncategorized |          0 |\n|       2 | Blogroll      | blogroll      |          0 |\n+---------+---------------+---------------+------------+\n2 rows in set (0.00 sec)<\/pre>\n
    Select a single record with a lot of fields for a close-up look<\/strong>:<\/p>\n
    mysql> select * from wp_links where link_id = 1\\G\n*************************** 1. row ***************************\n         link_id: 1\n        link_url: http:\/\/codex.wordpress.org\/\n       link_name: Documentation\n      link_image:\n     link_target:\nlink_description:\n    link_visible: Y\n      link_owner: 1\n     link_rating: 0\n    link_updated: 0000-00-00 00:00:00\n        link_rel:\n      link_notes:\n        link_rss:\n1 row in set (0.00 sec)<\/pre>\n
    While you have just installed a brand new WordPress, you’ll notice that there is already a bunch of data in the database.  That data consists of your configuration options, one post, one page, one comment, and a few blogroll links.  You might not need all those for your other blogs, but now all of that plays an important role – it shows you were things are and how they are related.<\/p>\n
    Look into every table.  Then change something.  Edit a post or add another comment.  Tag something.  But don’t do everything at once – one step at a time.  Check the data in the database after each step, and see how is it different from what you had there before you made a step.<\/p>\n
    10 minutes later you’ll know everything there is to know about WordPress database structure.  Enjoy!<\/p>\n\n","protected":false},"excerpt":{"rendered":"\n
    Almost two years ago I wrote a blog post titled “A look inside the WordPress database“.\u00a0 While a lot of people enjoyed it (and, apparently still do, even though it’s a bit outdated), I think it could be greatly simplified.\u00a0 And it will probably take you less time to understand WordPress database now than it … Continue reading Understanding WordPress database in 10 minutes<\/span><\/a><\/p>\n\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"_links_to":"","_links_to_target":""},"categories":[1,62,60],"tags":[3066,1905],"keyring_services":[],"class_list":["post-11605","post","type-post","status-publish","format-standard","hentry","category-general","category-technology","category-wordpress","tag-mysql","tag-wordpress-internals"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":11628,"url":"https:\/\/mamchenkov.net\/wordpress\/2009\/07\/09\/learning-wordpress-files-and-database\/","url_meta":{"origin":11605,"position":0},"title":"Learning WordPress : files and database","author":"Leonid Mamchenkov","date":"July 9, 2009","format":false,"excerpt":"There are a numerous blog posts and forum discussion about WordPress.\u00a0 However you can read all of them back and forward and still not know how the system works.\u00a0 If you seek the real understanding of WordPress, there are two and two things only that you need to go through\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"WordPress at PHPXref.com","src":"https:\/\/i0.wp.com\/mamchenkov.net\/wordpress\/wp-content\/uploads\/2009\/07\/wordpress_source_code.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":24657,"url":"https:\/\/mamchenkov.net\/wordpress\/2015\/08\/26\/how-does-a-relational-database-work\/","url_meta":{"origin":11605,"position":1},"title":"How does a relational database work","author":"Leonid Mamchenkov","date":"August 26, 2015","format":false,"excerpt":"\"How does a relational database work\" is an excellent (lengthy, technical, but simply written and well explained) article on some of the most important bits inside the relational database. \u00a0It's somewhat of a middle ground between a theoretical database discussion in college and vendor-specific documentation of a database engine. Though\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"databases","src":"https:\/\/i0.wp.com\/mamchenkov.net\/wordpress\/wp-content\/uploads\/2015\/08\/databases.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":24008,"url":"https:\/\/mamchenkov.net\/wordpress\/2015\/04\/27\/schemaspy-graphical-database-schema-metadata-browser\/","url_meta":{"origin":11605,"position":2},"title":"SchemaSpy – Graphical Database Schema Metadata Browser","author":"Leonid Mamchenkov","date":"April 27, 2015","format":"link","excerpt":"SchemaSpy - Graphical Database Schema Metadata Browser. \u00a0This is a tool written in Java that helps one to generate database schema documentation. \u00a0Have a look at some sample pages. \u00a0Those familiar with Graphviz will immediately realize that the tools is using dot for graphing tables and their relationships. \u00a0Those familiar\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":27005,"url":"https:\/\/mamchenkov.net\/wordpress\/2016\/11\/29\/magento-database-maintenance\/","url_meta":{"origin":11605,"position":3},"title":"Magento database maintenance","author":"Leonid Mamchenkov","date":"November 29, 2016","format":false,"excerpt":"If you are running a Magento-based website, make sure you add the database maintenance script to the cron. \u00a0For example, append this to the \/etc\/crontab: Thanks to this page, obviously. \u00a0You'll be surprised how much leaner your database will be, especially if you get any kind of traffic to the\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":23272,"url":"https:\/\/mamchenkov.net\/wordpress\/2015\/01\/07\/postgrest-rest-api-for-any-postgres-database\/","url_meta":{"origin":11605,"position":4},"title":"PostgREST – REST API for any Postgres database","author":"Leonid Mamchenkov","date":"January 7, 2015","format":"link","excerpt":"PostgREST - REST API for any Postgres database","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"logo","src":"https:\/\/i0.wp.com\/mamchenkov.net\/wordpress\/wp-content\/uploads\/2015\/01\/logo-500x108.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":25946,"url":"https:\/\/mamchenkov.net\/wordpress\/2016\/03\/16\/aws-database-migration-service\/","url_meta":{"origin":11605,"position":5},"title":"AWS Database Migration Service","author":"Leonid Mamchenkov","date":"March 16, 2016","format":false,"excerpt":"AWS Database Migration Service is yet another one of those tools that you always wished that somebody created, but never actually got to checking if it exists. \u00a0Here is a recent blog post showcasing the functionality. Do you currently store relational data in an on-premises Oracle, SQL Server, MySQL, MariaDB,\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"AWS Database Migration Service","src":"https:\/\/i0.wp.com\/mamchenkov.net\/wordpress\/wp-content\/uploads\/2016\/03\/dms_connect_endpoints_filled_3-500x409.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]}],"jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts\/11605","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/comments?post=11605"}],"version-history":[{"count":0,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts\/11605\/revisions"}],"wp:attachment":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/media?parent=11605"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/categories?post=11605"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/tags?post=11605"},{"taxonomy":"keyring_services","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/keyring_services?post=11605"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}