The PartialIndex (es) of MySQLInnoDB and PostgreSQL is different.

Source: Internet
Author: User
Tags percona
The PartialIndex of MySQLInnoDB is: bytes, typicallythefirstNcharacters (theprefix) ofalongVARCHARvalue. The PartialIndexes of PostgreSQL is: Apartialindexisanindexbuiltove

The Partial Index of MySQL InnoDB is: An index that represents only part of a column value, typically the first N characters (the prefix) of a long VARCHAR value. in PostgreSQL, Partial Indexes is: A partial index is an index built ove

MySQL InnoDB refers to the following Partial Index:

An index that represents only part of a column value, typically the first N characters (the prefix) of a long VARCHAR value.

PostgreSQL refers to the following Partial Indexes:

A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index ). the index contains entries only for those table rows that satisfy the predicate. partial indexes are a specialized feature, but there are several situations in which they are useful.

Finally, PostgreSQL can do what MySQL InnoDB's Partial Index wants, and more.

The Partial Index of MySQL InnoDB is set to the prefix index (n bytes before the string). The possible cause isCHAR(32)Only the first 16 bytes indexes are supported.

PostgreSQL's Partial Indexes has benefited from many aspects and is even larger. Because Indexes on Expressions exists, you can also index suffix, or even the value obtained by the index through the string function, in addition to the prefix index of MySQL.

For example, PostgreSQL can set "I only need to index the username of the person born in January 1 」:

CREATE INDEX test_index ON test_table (username) WHERE birth_month = 1 AND birth_day = 1;

In MySQL, the problem that indexes need to be adjusted anyway, or another table needs to be split and then indexed can be reduced by making full use of these features of PostgreSQL...

Related Posts:
  • Instagram explained the use of PostgreSQL's five features...
  • MySQL's question about VARCHAR Index space usage...
  • Percona's "Advanced MySQL Query Tuning 」...
  • MySQL 5.7...
  • When using Percona XtraBackup, Use compact mode to save space...

Original article address: the Partial Index (es) of MySQL InnoDB and PostgreSQL is different. Thank you for sharing it with me.

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: info-contact@alibabacloud.com 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.