Database optimization design _ PHP Tutorial

Source: Internet
Author: User
Database optimization design. Most of the time, we may change the field in the original database table structure as needed to meet our business needs. To put it simple, many times, we may add fields to the original database table structure as the requirements change constantly to meet our business needs.

Here is a simple example:

I need a database table to store the user information of a website. This user information needs to include (whether the account is an active account, whether the account is bound to a mailbox, whether the account has purchased products, and whether the account has expired .... normally, we may design this database table as follows:


  ()    AUTO_INCREMENT,       `F001` ()  ,       `F002` ()  ,       `F003`  ()  ,       `F004`  ()  ,        (InnoDB AUTO_INCREMENT  CHARSETutf8;

If you follow the above method for design, when the demand changes, such as adding a field, whether the account is valid. Then we need to add another field 'f005 ', which will cause great trouble in management. when adding a field, we need to update all or historical data, which can easily lead to data loss.

Then, how can we solve this problem and minimize the impact of association between data fields. One way is to integrate all these fields into one field. For example, the 'flag' field is stored in decimal format (first: 1: Active, 0: inactive. Second: 1: Bound, 2: not bound. Third place: 1: purchased products, 0: not purchased ....), explanation: If the value of this field is 5, the value is 101 in binary format, 1 in the first place, 0 in the second place, and 1 in the third place. this indicates that the account is "active, unbound, and purchased ". What are the advantages of this method? the advantage is that the association between various types is very small and it does not affect other fields because a field type is updated. So how can we query this design? Bitwise and, for example, when I want to query all purchased products and active accounts (the first and third places are 1 ), for other bits, enter 0, that is, 101 = 5:

SQL statement query:


   ACCOUNT  F005;

The preceding SQL statement can be used to query accounts that have purchased products and are active.

Then, you only need to update the binary bits to the required values in the background program. for example, the purchased binary bits are active, that is, 101, if you want to change it to purchased but not active, use (5 & 1) = 1, so that the third position is 0. Here we provide a PHP function that can handle the business of the program very well.


 = (2, (-1 () |! () |! () | (! = 0 &&! = (& (0xffff ^) |

Bytes. A simple example...

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.