CODEWITHSUNDEEP
mysqlsql
dataskills
dataskills

Reputation: 656

How can I optimize this further?

My table has approx. 121,246,211 rows. The records are simple page impression information.

Here is the schema:

create table stat_page
(
  id int auto_increment primary key,
  pageId int not null,
  timestamp int not null
)
  collate = utf8_unicode_ci;

create index pageIdIndex
  on stat_page (pageId);

create index timestampIndex
  on stat_page (timestamp);

This query takes 15 seconds:

select count(*)
from stat_page
where `timestamp` > 1543622400;

This query takes nearly 7 minutes:

select count(*)
from stat_page
where `timestamp` > 1543622400
and pageId = 87;

I thought I indexed the right things; is the table just too large? Does anyone have a suggestion as to how to get information from this table faster?

Upvotes: 2

Views: 71

Answers (1)

The Impaler
The Impaler

Reputation: 48769

The following index will improve the performance of that query:

create index ix1 on stat_page (pageId, timestamp);

This query benefits of this "composite" index.

Upvotes: 6

Related Questions