Discover MyXplain, another way to learn, search, find and stroll

 

 

I’m very glad to announce you that MyXplain is released today.

This project is the result of many weeks of work for my friend Max and I and we are obviously very proud to present it.

Please take a time to browse it and find all the surprises that awaiting you.

We wanted to make an unique place, a gathering place where you can find all you want to know about the most useful MySQL commands.
We started with the explain command because we consider it as one of the most important.
show processlist and show slave status will be the next proposed commands on MyXplain.

The concept is pretty simple, we would like to offer you a new documentation to access all needed informations, slides, books and links about a particular command, in a beautiful way.

And, the most important for us was to create something alive, you can propose your links on MyXplain or add comments. In other words, you can share your expertise and your experiences with the MySQL community.

Stay curious, passionate and take your time on MyXplain!

To give a chance to MyXplain, you can share this news with your friends, your mother in law and your colleagues.
We are convinced that this type of tools can help you, in one way or the other.

One more thing, stay tuned, follow us on twitter :


Thanks

MySQL 101 : Numeric type attributes and maximum value

I would like to make a quick MySQL 101 about numeric types in MySQL and especially explain the difference between the numeric type attributes and the maximum value that a numeric type can store.

Someone asked me yesterday why when he inserts a value in a table, the value 127 appears instead of the value 160 that he’s just inserted.
Ok, let me explain, consider this table :

What happens if you want to insert this row in the table :

sql > insert into product values (160, 554, "iPad mini");
Query OK, 1 row affected, 1 warning (0.01 sec)
sql > show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'id' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

sql > select * from product;
+-----+----------+-----------+
| id  | quantity | label     |
+-----+----------+-----------+
| 127 | 554      | iPad mini |
+-----+----------+-----------+
1 row in set (0.00 sec)

The result of the insert command suggest that a warning was detected during the insert process.
The warning message indicates that the value we wanted to insert in the id column was too high. There isn’t any mention of the quantity column.
And when I wanted to display the contents of the table, the value 127 appears instead of the value 160 previously inserted.

  • Value 160 inserted in a tinyint(8) column => 127 was stored in the table
  • Value 554 inserted in a int(200) column => 554 was stored in the table

The two values we wanted to insert for the id and the quantity columns were more important than the value of the type attribute.
But why there wasn’t any error for the quantity column ?

Simply because that attribute is not used to determine the maximum value that a numeric type can store.
The numeric type attribute may be used only by applications, not by MySQL.
The width of this attribute is present in the metadata returned with result sets of the query.

In our example, there was a warning for the id column and the data was truncated because the maximum value that a signed tinyint can store is 127.
Take a look at this table that shows the maximum values for each numeric types :
(Source : Official MySQL documentation)

You can see that the maximum value for a signed int is much larger than the value that we wanted to insert in the quantity column.

This question comes up frequently, hope this post can help.

Covering or not covering, that is the question

I’m currently working on a new project about the explain command output.
For my research I’ve read a lot of documentations, presentations and blog posts and I would like to focus on the join type in the explain output.
And in particular when type=index in this explain output.

Take a look at what says the official documentation about type=index :

Wait a second, only the index tree is scanned? really?
This description seems to be the definition of a covering index.
But a covering index is only characterized by a “Using index” in the extra column of the explain output.

Consider the t1 table, just two columns and a primary key on the id column :
(The table contain 5 lines)

Now, let see the explain output of this query :

sql > explain SELECT text1 FROM t1 ORDER BY id;

The join type is equal to index (type=index), the primary key is used (key=PRIMARY) but there isn’t any indication in the extra column.
The primary key is only used to sort the result-set of the query, five rows are return here (rows=5), this is the entire content of the table.

So, in this case, the index is used to sort the rows but the table is acceded to retrieve the values of the text1 column.
It’s not possible to say that only the index tree is scanned because the text1 column is not a part of the primary key, MySQL needs to access to the table.

Consider this other query :

sql > explain SELECT id FROM t1;

In this case, the type column indicates “index” and the extra column indicates “Using index”.
That means MySQL uses a covering index to retrieve data, the entire content of the index is read, only the index tree is read. MySQL doesn’t need to read the table because all the needed data are stored in the index.

The official documentation appears vague for this particular join type. When type=index, the table can be accessed yet for retrieve data.
Only the specification of “Using index” in the extra column means that only the index is accessed.

The real definition for this particular join type could be that the table is scanned in the index order (This is the definition given in the book High Performance MySQL)

Keep in mind that a full index scan is just a bit better than a full table scan but remains a bad way to access your data.