CODEWITHSUNDEEP
phpyii
user1342336
user1342336

Reputation: 977

issue to query huge dataset using Yii ADO

I have a table with over hundred of thousand of rows. When I use below:

Yii::app()->db->createCommand('SELECT * FROM thistable')->query();

The query failed, however testing much small tables, it was fine. I wanted to backup my current database, and I followed this link:

http://www.yiiframework.com/forum/index.php/topic/29291-full-database-backup/

Any idea? Thanks in advance!

Upvotes: 2

Views: 304

Answers (1)

ineersa
ineersa

Reputation: 3445

You need to avoid such queries in big tables. It can cause mysql crash or server dump. And thats not yii problem. Advices:

1.Use indexes (but carefully sometimes they slowing down queries - COUNT for example).

2.Always make limits in your queries : SELECT * FROM thistable LIMIT 50.

3.If you using InnoDB for total count use EXPLAIN SELECT COUNT(*) FROM thistable. In myISAM COUNT() works fast.

4.Avoid all joins. Even join on small table(<100 records) can cause 30-100% slow on queries.

I assume that 2nd point is your problem. For example:

SELECT * FROM thistable LIMIT 30 => result (32,415,735 all, Query took 0.0184 sec.)`

SELECT COUNT(*) FROM thistable'=> result (31,912,535 all, Query took 215.14 sec.)

Upvotes: 1

Related Questions