Showing entries 1 to 3
Displaying posts with tag: auto generated column (reset)
MySQL 5.7: Optimizer finds best index by expression

The optimizer in MySQL 5.7 leverages generated columns. Generated columns will physically store data in two cases: Either the column is defined as STORED or you create an index on a virtual column. The optimizer will leverage such an index automatically if it encounters the same expression in a statement. Let's see an example:

mysql> DESC squares;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| dx    | int(10) unsigned | YES  |     | NULL    |       |
| dy    | int(10) unsigned | YES  |     | NULL    |       |

[Read more]
Secondary Indexes on XML BLOBs in MySQL 5.7

When storing XML documents in a BLOB or TEXT column there was no way to create indexes on individual XML elements or attributes. With the new auto generated columns in MySQL 5.7 (1st Release Candidate available now!) this has changed! Let me give you an example. Let's work on the following table:

 mysql> SELECT * FROM country\G  
*************************** 1. row ***************************
docid: 1
doc: <country>
<name>Germany</name>
<population>82164700</population>
<surface>357022.00</surface>
<city name="Berlin"><population></population></city>
<city …
[Read more]
Auto Generated Columns in MySQL 5.7: Two Indexes on one Column made easy

One of my customers wants to search for names in a table. But sometimes the search is case insensitive, next time search should be done case sensitive. The index on that column always is created with the collation of the column. And if you search with a different collation in mind, you end up with a full table scan. Here is an example:

The problem

 mysql> SHOW CREATE TABLE City\G  
*************************** 1. row ***************************
Table: City
Create Table: CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
KEY `Name` (`Name`),
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT …
[Read more]
Showing entries 1 to 3