10 Millions of records

I have a large database and I’m trying to search for something specific but it’s timing out since it’s about 10 millions of records.

What is the best approach to do that in Yii2?

I’m doing this now:

$query = Stats::find()

				->where(['id' => $id])

				->andWhere(['>=', 'datetime', $from_date])

				->andWhere(['<=', 'datetime', $to_date])

				->orderBy(['id' => SORT_DESC])

I’m displaying the results in a grid and I thought that Yii2 was smart enough to not query all the 10 million of records. I thought that it would query page by page since pagination is enabled in the grid.

Please help me.

How long does the query take if you run it independently of a grid?

OH NOOOOO!!!! Your query will try to return all matching records!!!!

How many records do you think will match?

You need to do some more work to tell YII what you want to do.

For a start read

http://www.yiiframework.com/doc-2.0/guide-output-pagination.html

Once you have some code, if you still have problems, then you can come back and ask more questions.

With 10 millions records, you have to pay very close attention to your queries or you could end up with timeouts or memory full errors.

Good luck

-John

Is there an existing db index on "id+datetime"?

Tri thank you so much. I had the index just for the id but I forgot to add ID + datetime. Now it loads in like 1 second.

John thank you. That helped a lot as well. I did what Tri told me + what you told me and it works like a charm.

Great! Glad we could help.

-John