Indexing a MYSQL table with many columns

Your schema has been oddly designed. Your current setup may make for easy reporting, but you're correct, indexing like you've suggested will create terrible insert and update performance.

My suggestion would be to refactor this table. Instead of your current setup, I would suggest that you change the posts table to have the columns (start_at, day). In this design, day can be Sun through Sat, and you only need one index (start_at, day).

If you really need the current format for reading/reporting reasons (I wouldn't blame you), you could always setup a view that pivots the data in that manner.

All of that being said, not every field you qualify on needs to be in an index. My estimation is that the vast majority of your table's rows will be filterable simply by the date. The few that are left probably don't need to be indexed for decent read performance.

In a different vein, why even store the day? That can be calculated based off the date.

AS another said, may be it should be sufficient to index for start_at only.

If you want to investigate on index which include the post publishing day (in particular if your table has lot of field and you want to list only fields start_at and the day of publishing), you could create a table like this:

CREATE TABLE XX (start_at ..., which_day tinyint UNSIGNED); 

and an index like this

CREATE INDEX XX_IX01 (start_at, which_day); 

where which_day can be, using a sort of coding:

0=no day 2^0=sun 2^1=mon 2^2=.. ... 2^6=sat

So, for example if the mon and sat are 1, the value should be 2^1 +2^6=2+64=66.

If possibile avoid SELECT * and write the field you need.

Use EXPLAIN to check how db engine use your index/tables.

This link could be useful: How can I index these queries?

Sample data:

CREATE table et1 (start_at DATE, which_day tinyint UNSIGNED); CREATE INDEX ET1_IX1 ON et1(start_at, which_day); INSERT INTO et1 VALUES ('20170701', 132); INSERT INTO et1 VALUES ('20170702', 3); INSERT INTO et1 VALUES ('20170704', 5); INSERT INTO et1 VALUES ('20170630', 2); SELECT start_at, which_day, which_day & 128 AS X FROM et1; SELECT start_at, which_day, which_day & 128 AS X FROM et1 WHERE start_at>'20170630' AND which_day & 128 =128; EXPLAIN SELECT * FROM et1 WHERE start_at>'20170630' AND which_day & 128 =128; DROP table et1; 

You are over complicating your database schema, there is no need to add all those columns, nor to create a ton of indexes.

You can simplify your post table as:

id | title | (...) | start_at 

You can then replace this query:

SELECT * FROM posts WHERE start_at >= '2017-02-01' AND mon = 1 

and instead use the DAYOFWEEK function:

SELECT * FROM posts WHERE start_at >= '2017-02-01' AND DAYOFWEEK(start_at) = 2 

(1 = Sunday, 2 = Monday, …, 7 = Saturday).

You can then keep a single index on the start_at column.