{"id":287,"date":"2016-08-07T03:18:02","date_gmt":"2016-08-07T00:18:02","guid":{"rendered":"https:\/\/community.virtono.com\/?p=287"},"modified":"2023-06-21T10:20:36","modified_gmt":"2023-06-21T07:20:36","slug":"mysql-top-10-design-tips","status":"publish","type":"post","link":"https:\/\/www.virtono.com\/community\/knowledgebase\/mysql-top-10-design-tips\/","title":{"rendered":"MySQL: Top 10 Design Tips"},"content":{"rendered":"

This article is a fraction of a Number of Articles on MySQL, to access them click here<\/a>.<\/h3>\n

\u00a0\u00a0\u00a0\u00a0\u00a0 1.Understand Your Technology Tools<\/strong><\/p>\n

MySQL is great platform to manage your database, and to be able to understand every aspect is not that easy, but the one thing that we can do is that, we can try our best to find what those are, specifically focusing on those aspects that will maximize MYSQL\u2019s strength catering to our needs.<\/p>\n

Here are some general points :<\/p>\n

\u2756 Maximize MySQL strengths<\/p>\n

\u2756 Scale out \/ HA Options<\/p>\n

\u2756 Different Storage Engines<\/p>\n

\u2756 Query Cache<\/p>\n

\u2756 Minimize MySQL weaknesses<\/p>\n

\u2756 No Online Alter<\/p>\n

\u2756 Backup Strategies<\/p>\n

\u2756 Instrumentation<\/p>\n

Disk = Memory = Performance<\/p>\n

\u201cSave that Memory\u201d<\/p>\n

When going over the questions like \u201cWhich data to use?\u201d always remember that every single counts, because your database will always keep on growing. So make sure to keep these points in your mind :<\/p>\n

\u2756Average 25% – 30% saving on engagements<\/p>\n

\u2756Better 60% (200GB System)<\/p>\n

\u2756Best 78% (8GB per master with 12 masters)<\/p>\n

Less disk accesses and more data in memory<\/p>\n

    \n
  1. Choosing the data<\/strong><\/li>\n<\/ol>\n

    To design a database, you first must identify what information belongs in it.<\/p>\n

    The database must contain the data needed for the website to perform its purpose.<\/p>\n

    Here are a few examples:<\/p>\n

    \u2726 An online catalog needs a database containing product information.<\/p>\n

    \u2726 An online order application needs a database that can hold customer information and order information.<\/p>\n

    \u2726 A travel website needs a database with information on destinations, reservations, fares, schedules, and so on.<\/p>\n

    In many cases, your application might include a task that collects information from the user. For instance, customers who buy products from a website must provide their address, phone number, credit card information, and other data in order to complete the order. The information must be saved at least until the order is filled. Often, the website retains the customer information to facilitate future orders so the customer doesn\u2019t need to retype the information when placing the next order. The information also provides marketing opportunities to the business operating the website, such as sending marketing offers or newsletters to customers.<\/p>\n

    \u2756MySQL has 9 numeric data types<\/p>\n

    \u2756 Oracle for example has only 1<\/p>\n

    \u2756Integer: TINYINT, SMALLINT,<\/p>\n

    MEDIUMINT, INT, BIGINT<\/p>\n

    \u2756Floating Point: FLOAT, DOUBLE<\/p>\n

    \u2756Fixed Point: DECIMAL<\/p>\n

    \u2756Other: BIT, (ENUM maybe)<\/p>\n

    \u2756Favorite signs of poor design<\/p>\n

    \u2756 INT(1)<\/p>\n

    \u2756 BIGINT AUTO_INCREMENT<\/p>\n

    \u2756 no UNSIGNED used<\/p>\n

    \u2756 DECIMAL(31,0).<\/p>\n

    \u2756INT(1) – 1 does not mean 1 digit<\/p>\n

    \u2756 (1) represents client output display format only<\/p>\n

    \u2756 INT is 4 Bytes, TINYINT is 1 Byte<\/p>\n

    \u2756 TINYINT UNSIGNED can store from 0 \u2013 255<\/p>\n

    \u2756 BIT is even better when values are 0 \u2013 1<\/p>\n

    \u2756 BIGINT is not needed for AUTO_INCREMENT<\/p>\n

    \u2756INT UNSIGNED stores 4.3 billion values<\/p>\n

    \u2756 You should be partitioning when at billions of rows<\/p>\n

    \u2756BIGINT is applicable for some columns<\/p>\n

    \u2756 e.g. summation of values<\/p>\n

    \u2756Best Practice<\/p>\n

    \u2756 All integer columns UNSIGNED unless there is a<\/p>\n

    reason otherwise<\/p>\n

    \u2756 Adds a level of data integrity for negative values<\/p>\n

      \n
    1. Application Data Type Efficiencies<\/strong><\/li>\n<\/ol>\n

      \u00a0<\/strong><\/p>\n

      \u201cData Efficiency<\/em><\/strong>\u00a0<\/em>refers to efficiency of the many processes that can be applied to\u00a0<\/em>data<\/em><\/a>\u00a0<\/em>such as storage, access, filtering, sharing, etc., and whether or not the processes lead to the desired outcome within resource constraints.\u201d \u2013 Wikipedia<\/em><\/p>\n

      \u00a0<\/em><\/p>\n

      So by Data Efficiency in MySQL, we mean to use the most basic, really necessary, expandable data type for categorizing, storing and manipulating the data stored in your database.<\/p>\n

      Some Examples are:<\/p>\n

      \u2756Using Codes or ENUM<\/p>\n

      \u2756 A description is a presentation layer function<\/p>\n

      \u2756 e.g. ‘M’, ‘F’ instead of ‘Male’, ‘Female’<\/p>\n

      \u2756 e.g. ‘A’, ‘I’ instead of ‘Active’, ‘Inactive’<\/p>\n

      \u2756BINARY(16\/20) v CHAR(32\/40)<\/p>\n

      \u2756 MD5() or HASH() Hex value with twice the length<\/p>\n

      \u2756INT UNSIGNED for IPv4 address<\/p>\n

      \u2756 VARCHAR(15) results in average 12 bytes v 4 bytes<\/p>\n

        \n
      1. NOT NULL<\/strong><\/li>\n<\/ol>\n

        Always use NOT NULL unless there is a reason not to, because this will prevent a lot your database from a lot of \u201cbad\u201d.<\/p>\n

        \u2756Saves up to a byte per column per row of data<\/p>\n

        \u2756Double benefit for indexed columns<\/p>\n

        \u2756Don’t use frameworks or tools<\/p>\n

        \u2756NOT NULL DEFAULT ” is bad design<\/p>\n

          \n
        1. Be Wary of TEXT\/BLOB<\/strong><\/li>\n<\/ol>\n

          Its important to understand the difference between the BLOB and TEXT, so you can use them properly.<\/p>\n

          A BLOB is a binary string to hold a variable amount of data. For the most part BLOB’s are used to hold the actual image binary instead of the path and file info. Text is for large amounts of string characters. Normally a blog or news article would constitute to a TEXT field.<\/p>\n

          Here is more on that http:\/\/dev.mysql.com\/doc\/refman\/5.7\/en\/blob.html<\/p>\n

          Problems that may arise due to this:<\/p>\n

          \u2756Data collection options<\/p>\n

          \u2756Incomplete Options<\/p>\n

          \u2756 Slow Query Log<\/p>\n

          \u2756 SHOW PROCESSLIST<\/p>\n

          \u2756 Application level logging<\/p>\n

          \u2756 General Lag<\/p>\n

            \n
          1. Transactions<\/strong><\/li>\n<\/ol>\n

            A transaction in MySQL is an operation conducted sequentially focused on database manipulation, performed as a single task or work unit.\u00a0 A transaction will never be complete unless each individual operation within the work unit is successful. If any operation within the transaction fails, the entire transaction will fail.<\/p>\n

            So make sure to:<\/p>\n

            \u2756Always design for transactions<\/p>\n

            \u2756Always use transactions<\/p>\n

            \u2756Use a transactional storage engine<\/p>\n

              \n
            1. Data Integrity is Key<\/strong><\/li>\n<\/ol>\n

              The term integrity refers to the accuracy or correctness of data in the database. Integrity constraints ensure that the changes made to the database by authorized users do not result in a loss of data consistency. Thus, the integrity constraints guard against accidental damages to the databases. A database maybe subjected to any number of integrity constraints of arbitrary complexity.<\/p>\n

                \n
              1. Leverage the Query Cache<\/strong><\/li>\n<\/ol>\n

                Query Cache can be a great benefit. Deterministic v Non Deterministic SQL<\/p>\n

                MySQL Query Cache is not the only type of caching you should consider.<\/p>\n

                  \n
                1. Naming Standards<\/strong><\/li>\n<\/ol>\n

                  Naming a databases\u2019 keys, integrities etc, is really important not only for you but for others who you may choose to work with you, so keep it simple and easy, and managae a metadata.<\/p>\n

                  Here are some key points:<\/p>\n

                  \u2756Name all Primary Key\u02bcs Uniquely<\/p>\n

                  \u2756 e.g. customer_id, order_id not id<\/p>\n

                  \u2756Use Data Dictionary SQL to verify data types<\/p>\n

                  \u2756 Data Types & Lengths<\/p>\n

                  \u2756Be Descriptive<\/p>\n

                  \u2756 e.g. invoice_date not just date<\/p>\n

                  \u2756Avoid Reserved Words<\/p>\n

                  \u2756 e.g. date, time, timestamp<\/p>\n

                    \n
                  1. Testing, Testing, Testing<\/strong><\/li>\n<\/ol>\n

                    The goal of a testing environment is not to test your software, it is to break your software until you know what might happen in the future and you can already make a plan around it!<\/p>\n

                    \u2756You must have a testing environment<\/p>\n

                    \u2756Testing on a Production server is not an option<\/p>\n","protected":false},"excerpt":{"rendered":"

                    This article is a fraction of a Number of Articles on MySQL, to access them click here. \u00a0\u00a0\u00a0\u00a0\u00a0 1.Understand Your Technology Tools MySQL is great platform to manage your database, and to be able to understand every aspect is not that easy, but the one thing that we can do<\/p>\n","protected":false},"author":3,"featured_media":292,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[5],"tags":[],"class_list":["post-287","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-knowledgebase"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/MySQL_Replication1-2.png?fit=965%2C687&ssl=1","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7ISfL-4D","jetpack_likes_enabled":true,"jetpack-related-posts":[{"id":3496,"url":"https:\/\/www.virtono.com\/community\/tutorial-how-to\/how-to-tune-your-mysql-server-for-optimal-performance\/","url_meta":{"origin":287,"position":0},"title":"How to Tune Your MySQL Server for Optimal Performance","author":"George B.","date":"June 12, 2023","format":false,"excerpt":"In this step-by-step guide, we will walk you through the process of tuning your MySQL server, helping you enhance its efficiency and responsiveness. MySQL is one of the most popular open-source relational database management systems, widely used for various applications and websites. However, as your database grows in size and\u2026","rel":"","context":"In "Tutorials"","block_context":{"text":"Tutorials","link":"https:\/\/www.virtono.com\/community\/category\/tutorial-how-to\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2023\/06\/1.png?fit=600%2C330&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2023\/06\/1.png?fit=600%2C330&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2023\/06\/1.png?fit=600%2C330&ssl=1&resize=525%2C300 1.5x"},"classes":[]},{"id":305,"url":"https:\/\/www.virtono.com\/community\/knowledgebase\/how-mysql-and-php-work-together\/","url_meta":{"origin":287,"position":1},"title":"How MySQL and PHP Work Together","author":"Daniel Draga","date":"August 7, 2016","format":false,"excerpt":"This article is a fraction of a Number of Articles on MySQL, to access them click here. You interact with the database by passing messages to the MySQL server. The messages are composed in the SQL language, a standard computer language understood by most database management systems. PHP doesn\u2019t understand\u2026","rel":"","context":"In "Knowledgebase"","block_context":{"text":"Knowledgebase","link":"https:\/\/www.virtono.com\/community\/category\/knowledgebase\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/php___mysql_wallpaper_by_milesandryprower-d9o6yat.png?fit=1024%2C576&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/php___mysql_wallpaper_by_milesandryprower-d9o6yat.png?fit=1024%2C576&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/php___mysql_wallpaper_by_milesandryprower-d9o6yat.png?fit=1024%2C576&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/php___mysql_wallpaper_by_milesandryprower-d9o6yat.png?fit=1024%2C576&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":1033,"url":"https:\/\/www.virtono.com\/community\/tutorial-how-to\/how-to-install-and-configure-mysql-on-opensuse-13-1\/","url_meta":{"origin":287,"position":2},"title":"How To Install and Configure MySQL on openSuSe 13.1","author":"Daniel Draga","date":"September 24, 2017","format":false,"excerpt":"Introduction MYSQL is a software, with MySQL server at its core, and a lot of utility programs, that helps is managing and administration of database. For example, let say you want to create a new database, you send a message to the MySQL server that says, for instance, \u201ccreate a\u2026","rel":"","context":"In "Tutorials"","block_context":{"text":"Tutorials","link":"https:\/\/www.virtono.com\/community\/category\/tutorial-how-to\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2017\/09\/mysql-1.jpg?fit=700%2C256&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2017\/09\/mysql-1.jpg?fit=700%2C256&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2017\/09\/mysql-1.jpg?fit=700%2C256&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2017\/09\/mysql-1.jpg?fit=700%2C256&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":1030,"url":"https:\/\/www.virtono.com\/community\/tutorial-how-to\/how-to-install-and-use-mysql-on-debian-7\/","url_meta":{"origin":287,"position":3},"title":"How To Install and Use MySQL on Debian 7","author":"Daniel Draga","date":"September 24, 2017","format":false,"excerpt":"Introduction MYSQL is a software, with MySQL server at its core, and a lot of utility programs, that helps is managing and administration of database. For example, let's say you want to create a new database, you send a message to the MySQL server that says, for instance, \u201cCreate a\u2026","rel":"","context":"In "Tutorials"","block_context":{"text":"Tutorials","link":"https:\/\/www.virtono.com\/community\/category\/tutorial-how-to\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2017\/09\/mysql-backup.png?fit=394%2C315&ssl=1&resize=350%2C200","width":350,"height":200},"classes":[]},{"id":277,"url":"https:\/\/www.virtono.com\/community\/knowledgebase\/introduction-to-mysql-and-how-it-works\/","url_meta":{"origin":287,"position":4},"title":"Introduction To MySQL and How It Works","author":"Daniel Draga","date":"August 7, 2016","format":false,"excerpt":"This article is a fraction of a Number of Articles on MySQL, to access them click here. INTRODUCTION Beneath all the cute animations, the smooth transactions, lies the ruins of the backend, and talking about websites, the backend is primarily the database. Ever wondered, how the web sites remember you?\u2026","rel":"","context":"In "Knowledgebase"","block_context":{"text":"Knowledgebase","link":"https:\/\/www.virtono.com\/community\/category\/knowledgebase\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/mysql_wallpaper_by_milesandryprower-d9o6y9z.png?fit=1024%2C576&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/mysql_wallpaper_by_milesandryprower-d9o6y9z.png?fit=1024%2C576&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/mysql_wallpaper_by_milesandryprower-d9o6y9z.png?fit=1024%2C576&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2016\/08\/mysql_wallpaper_by_milesandryprower-d9o6y9z.png?fit=1024%2C576&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":3746,"url":"https:\/\/www.virtono.com\/community\/tutorial-how-to\/how-to-create-a-user-and-grant-permissions-in-mysql\/","url_meta":{"origin":287,"position":5},"title":"How to create a user and grant permissions in MySQL","author":"George B.","date":"August 17, 2023","format":false,"excerpt":"Managing users is an integral part of database administration. It involves adding new users, giving them access, and, if necessary, deleting them. In this tutorial, we'll examine MySQL's user management procedure, which makes it the most widely used relational database management system available as open-source software. Creating a New User\u2026","rel":"","context":"In "Tutorials"","block_context":{"text":"Tutorials","link":"https:\/\/www.virtono.com\/community\/category\/tutorial-how-to\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.virtono.com\/community\/wp-content\/uploads\/2023\/08\/Create-a-user-and-grant-permissions-in-MySQL.png?fit=360%2C240&ssl=1&resize=350%2C200","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/posts\/287","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/comments?post=287"}],"version-history":[{"count":3,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/posts\/287\/revisions"}],"predecessor-version":[{"id":3569,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/posts\/287\/revisions\/3569"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/media\/292"}],"wp:attachment":[{"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/media?parent=287"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/categories?post=287"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.virtono.com\/community\/wp-json\/wp\/v2\/tags?post=287"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}