test.sql

use teamcom_shard_01;

DROP TABLE IF EXISTS contacts_test;

CREATE TABLE contacts_test (
  email VARCHAR(255) NOT NULL,
  public TINYINT(1), -- Values are either NULL, or True
  workspace_id INT(11) unsigned NOT NULL,
  creator INT(11) unsigned NOT NULL
  , UNIQUE KEY `only_one_public` (workspace_id, public, email)
  , UNIQUE KEY `creator_and_email` (workspace_id, creator, email)
);

EXPLAIN EXTENDED SELECT *
                   FROM contacts_test
		  WHERE workspace_id = 1234
		    AND (public = 1)
		     \G;

-- Expected that the `workspace_id, public, email` index will be used. However,
-- MySQL prefers the `workspace_id, creator, email` index.

show keys from contacts;