How can I optimize this further?

181
January 03, 2019, at 11:00 PM

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?

Answer 1

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.

Rent Charter Buses Company
READ ALSO
How to convert value on tables with mysql into json file with trigger?

How to convert value on tables with mysql into json file with trigger?

I use phpmyadmin for handle the mysql databaseI have got a table that call NodeJS and the column of this table calls id and number

176
Need help translating a MYSQL DB Sql Query into a Jpql query

Need help translating a MYSQL DB Sql Query into a Jpql query

I have 3 entities - usergroup, user, usergroupmembershipsusergroupmemberships has a 1 to 1 to both user and usergroup

172
Inserting data to database from dropdown menu whose data was imported from another database

Inserting data to database from dropdown menu whose data was imported from another database

i have created dropdown menu where options are inputted from database, however, i want the chosen option to be inputted to another database

163
Subquery - W3Resources

Subquery - W3Resources

I was wondering if my code works or not? Question #3 on this w3school exercise :

165