Large database speed issue

Yii 1.1 General Discussion
1

Hi All,

I am working of database which is having large database. The database having millions of rows.

How can I handle the speed and load time issue for these records ?

Thanks in advance.

2

You want to reduce your website load time?

Load time is a time which passed from the moment when user requested a website, until the website is fully loaded.

  • [size=“2”]You definatelly don’t want to load million of rows in one page, which means limit results to 10 or 50 results per page. This will consume less memory and your ‘load time’ will be smaller. [/size]
  • [size="2"]On large scale of rows, use lower DB abstraction layer not Active Directory classes.[/size]
  • [size="2"]From the database select only the columns you really need to display. [/size]
  • [size="2"]Try to optimize your database itself. Change the column types, add indexes etc… [/size]
  • [size="2"]Optimize your PHP code, and try to reduce number of DB queries (I saw lot of people making for loop and then calling DB count() inside it. They could simply call COUNT before loop and use it in the code)[/size]
  • [size="2"]Do a profiling to see where is your app bottle neck, and then try to optimize that part. [/size]
  • [size="2"]Configure your server to send compressed content[/size]
  • [size="2"]Optimize your css and JS files[/size]
3

It’s a very broad topic, entire books have been written about it by database experts and it usually requires application and database schema specific solutions. Some points to start with:

http://use-the-index-luke.com

http://database-prog…n-patterns.html

And, as duri already mentioned, object-relational mapping solutions like Yii’s Active Record don’t play well with large result sets and complex queries. You may have to write some carefully optimized SQL manually.

4

Thanks!!!

However how to deal with search functionality. As the database will grow large searching with different criteria taking too much time

5

Your first task is to optimize your queries, and make sure you defined proper type for every db column. For example, [size=2]I remember one database having milion of rows in one table. Latitude and longitude were defined as varchar type. I did some measurements before I changed to float, and beleave me some pages were faster for 5 seconds.[/size]

Also, you should think of implementing database replication. Yii 2 supports this kind of architecture which is grat. Here is article: http://www.yiiframework.com/doc-2.0/guide-db-dao.html#replication-and-read-write-splitting

6

Is it a keyword search or an "advanced search"-type form where users can specify complex filtering conditions?