|
Planet MySQL
|
Planet MySQL - http://www.planetmysql.org/
|
-
Log Buffer #272, A Carnival of the Vanities for DBAs
It is evident and beyond doubt now that the new media technologies like Twitter and Facebook are not going to wipe-out the blogs, rather they are complimenting each other very nicely and it seems they were made for each other. This Log Buffer Edition enhances this match, and presents you Log Buffer #272. Oracle: It [...]
-
Bug.mysql.com and Contributions!
Oracle enhanced the bugs.mysql.com site to provide a better experience for users to submit contributions !A new 'Contributions' tab has been added to the bugs.mysql.com user interface. This tab will allow users to have a defined space for their contributions. An Oracle Contributor Agreement (OCA) will still be required for all contributions. If needed, the OCA FAQ is posted here. Please take advantage of this new feature when you help support and enhance MySQL !
-
MySQL Handshake and Encryption
Interestingly, I have given the presentation on MySQL and Security at least 4 times in the past 6 weeks* and it was only last night, with the sharp minds at Baron’s Central Virginia MySQL Meetup Group (sadly Baron was not there!), that someone asked about when encryption happens in the MySQL handshake.
We had been talking about how MySQL authenticates users, and how if there are no ACL’s set for a given host, MySQL will reject connections from that host – even “telnet host 3306″ will be refused – and that’s when a clever audience member asked where in the handshake process encryption started. Is it before the username is sent? Before the password is sent? Does it encrypt all traffic, even the handshake traffic?
I think that’s an excellent question, and I know there’s a few sharp minds out there who probably know the answer….otherwise I will research the answer this weekend, when I’m back home in Boston.
* Effective MySQL User Group, as part of a tutorial for Percona Live: MySQL Conference and Expo , at the Professional IT Community Conference last week, and last night at the Central Virginia MySQL Meetup Group
-
Meet the MySQL Experts Podcast: MySQL Replication Global Transaction Identifiers & HA Utilities
In the latest episode
of our “Meet The MySQL Experts” podcast, Luis Soares,
Engineering Manager of MySQL Replication discusses the new Global Transaction
Identifiers (GTIDs) that are part of the latest MySQL 5.6 Development Release. We are also joined by Chuck Bell who
discusses how the new MySQL HA utilities use GTIDs to create a self-healing
replication topology.
In the podcast, we cover how GTIDs and the HA utilities are implemented, how they are
configured and considerations for their use.
You can also learn
more from Luis’ blog on GTIDs in MySQL 5.6 and Chuck’s blog on the HA utilities.
Of course, GTIDs are
just one of the major new features of MySQL replication. For a complete
overview, take a look at our DevZone article: MySQL 5.6 Replication - Enabling the Next Generation of Web & Cloud
Services.
You can try out MySQL
5.6 and GTIDs by downloading the Development Release (select Development Release tab)
Enjoy the GTID podcast and let us know what topics you would like covered in future podcasts! Also check out the library of Meet the MySQL Experts podcasts
-
Can MySQL use primary key values from a secondary index?
In the article about the role of a primary key, I mentioned that a secondary index in an InnoDB table consists not only of the values of its member columns, but also values of the table’s primary key are concatenated to the index. I.e. the primary key contents is part of every other index.
Assuming the following table structure:
CREATE TABLE `bets` (
`id` int(10) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
`game_id` int(10) unsigned NOT NULL,
...
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB
Here is the visualization:
If MySQL could use in queries these implicitly added values, it would maybe allow to save some space on listing the primary key columns at the end of an index explicitly. Let’s check various cases.
Row filtering
mysql> EXPLAIN
-> SELECT *
-> FROM bets
-> JOIN games
-> ON games.id = bets.id
-> WHERE bets.user_id = 111
-> AND bets.id > 3476\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bets
type: ref
possible_keys: user_id
key: user_id
key_len: 4
ref: const
rows: 22
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: games
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: game.bets.id
rows: 1
Extra:
Both key_len and ref fields indicate that only one four bytes long column is used from the user_id index. MySQL cannot use the primary key values in a secondary index for filtering in WHERE clause.
Sorting with ORDER BY
mysql> EXPLAIN
-> SELECT *
-> FROM bets
-> JOIN games
-> ON games.id = bets.game_id
-> WHERE bets.user_id = 111
-> ORDER BY bets.id DESC\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bets
type: ref
possible_keys: user_id
key: user_id
key_len: 4
ref: const
rows: 22
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: games
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: game.bets.game_id
rows: 1
Extra:
Extra only returns Using where, but there is no Using filesort. It means ORDER BY will be optimized using the hidden primary key data from the secondary index.
Aggregating with GROUP BY
mysql> EXPLAIN
-> SELECT *
-> FROM bets
-> JOIN games
-> ON games.id = bets.game_id
-> WHERE bets.user_id = 111
-> GROUP BY bets.id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bets
type: ref
possible_keys: user_id
key: user_id
key_len: 4
ref: const
rows: 22
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: games
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: game.bets.game_id
rows: 1
Extra:
Also in this case Extra neither shows Using filesort nor Using temporary, which would indicate no index is used for grouping. Therefore MySQL can optimize GROUP BY on the concatenated primary key values.
Covering index
mysql> EXPLAIN
-> SELECT bets.id
-> FROM bets
-> WHERE bets.user_id = 111\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: bets
type: ref
possible_keys: user_id
key: user_id
key_len: 4
ref: const
rows: 22
Extra: Using index
The query execution plan confirms through Using index that it will only need index contents to return result. MySQL can read and return the hidden primary key values to avoid the additional data lookup.
Summary
In InnoDB tables each entry of a secondary index always contains a copy of the corresponding primary key value. These values may in some cases be used to the benefit of query execution plan:
for ORDER BY on the primary key column(s)
for GROUP BY on the primary key column(s)
when returning the primary key column(s) values in the SELECT list
MySQL cannot use them, however, to optimize filtering in WHERE.
|
