7NVwHkbn

1use teamcom_shard_01;
2
3DROP TABLE IF EXISTS contacts_test;
4
5CREATE TABLE contacts_test (
6  email VARCHAR(255) NOT NULL,
7  public TINYINT(1), -- Values are either NULL, or True
8  workspace_id INT(11) unsigned NOT NULL,
9  creator INT(11) unsigned NOT NULL
10  , UNIQUE KEY `only_one_public` (workspace_id, public, email)
11  , UNIQUE KEY `creator_and_email` (workspace_id, creator, email)
12);
13
14EXPLAIN EXTENDED SELECT *
15                   FROM contacts_test
16		  WHERE workspace_id = 1234
17		    AND (public = 1)
18		     \G;
19
20-- Expected that the `workspace_id, public, email` index will be used. However,
21-- MySQL prefers the `workspace_id, creator, email` index.
22
23show keys from contacts;