CODEWITHSUNDEEP
mysqlindexing
Stas Coder
Stas Coder

Reputation: 347

Indexing a MYSQL table with many columns

I have a table posts with the following columns:
start_at, sun, mon, tue, wen, thu, fri, sat

start_at is of date type, the others - tinyint.

I make a query the following way: 

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

That is after second AND I set proper day of week dynamically.

What is the best way to index this table? Do I need to make a composite index for each day of a week, for example:

CREATE INDEX mon_index ON posts(start_at,mon);
CREATE INDEX sun_index ON posts(start_at,sun);

If I create 7 indexes for each day of the week, I think INSERT and UPDATE will be really slow.

UPDATED: I do need column for each day of week, because a user sets on which days post will be published. There can be a few days at the same time.

Upvotes: 2

Views: 294

Answers (3)

Cyril Graze
Cyril Graze

Reputation: 3890

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.

Upvotes: 0

etsa
etsa

Reputation: 5060

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;

Upvotes: 0

Jacobm001
Jacobm001

Reputation: 4539

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.

Upvotes: 3

Related Questions