Tag Info

New answers tagged

0

By default, an index key for a single-column index can be up to 767 bytes –MySQL Reference Manual The size of your index is: 191 (characters) * 4 (utf8mb4 max size per character) + 4 (length) + 1 (NULL) = 769 The calculation may not be 100% exact, I am not fully sure about the length requirements, but I will assume it is not a bug. The reason ...


3

Non-clustered indexes use the clustered index key value as a pointer back to the data. If you create a compound clustered index, you are making your non-clustered indexes that much larger. When choosing a clustered index, especially on tables with hundreds of millions of rows, try to choose a single narrow column if possible. i.e. choose an integer over a ...


2

To enforce unique email addresses, I would remove all competing email columns and store them in one central email table for all active emails. And another table for deleted emails: CREATE TABLE users ( user_id serial PRIMARY KEY , username text UNIQUE NOT NULL , email text UNIQUE -- FK added below -- can also be NOT NULL ); CREATE TABLE email ( ...


1

One way to think of this would be that you have two classes of user which have slightly different rules: deleted and not deleted. Deleted users e-mails may clash, not deleted users e-mails must be unique. Because these two classes have different rules (i.e. constraints), instead of using a flag to indicate whether or not a user is deleted, I would duplicate ...



Top 50 recent answers are included