how to increase performance of mysql query if we have more than 1 million records?

In User table i have more than 1 milli开发者_JS百科on records so how can i manage using MySQL, Symfony 1.4. Make performance better.

So that it can give quick output.

To significantly improve performance of well designed system all you can do is increase the resources. Typically, these days, the cheapest way to do this is to distribute the task.

For example a slow thing in RDBM system is reading and writing to an from the storage (typically RDBMs systems start as I/O bound, that is, they mostly wait for data to get read or written to storage).

So, to offset, very commonly the RDBMS will allow you to split the table across multiple HDDs, effectively multiplying the I/O performance (approach similar to RAID0).

Adding more hard disks increases the performance. This goes on up to maximum I/O that your system could support (either simply because the system can not push more data through circuits or because it does need to crunch the numbers a bit when it fetches them so it becomes CPU bound; optimally you would be utilising both)

After that you have to start multiplying the systems distributing the data across database nodes. For this to work either RDBMS must support it or there should be application layer that will coordinate distributing the tasks and merging the results, but normally things would still scale.

I would say that with 512 systems you could have all trillion records effectively cached (10^12) and achieve relatively nice performance. But really you should specify what kind of performance you are looking for - there is a difference between full text searches on terra-records and running mostly simple fetches and updates. Also, for certain work 500ms (or even more) is considered good performance and then for other work it would be horrible.

at first: theres a big difference between 1 trillion and 1 million.

to your performance problems: show us the query thats running slow, without seeing it, it's hard to tell whats wrong with it. what you could try:

• use EXPLAIN to get more information about your slow querys, see if they're using your indexes or if not (and if not, why not?)
• use correct and reasonable indexes