How to speed up database queries?

[christian90]

Hello everyone, im new in this forum and looking for some help.
I have database that has over 100k records and my website its.. lazy. I think its something with queries and database optimization. So there is my question
It is better to use two or three database for information? or single?
What about queries, its better to recieved data from one connection or make multiply connection with multiply queries?
What about computer, because maybe if I buy new computer it would go alot smoother, and is there some better optimization commands to find data?

[traq]

Welcome to DynamicDrive.

To start with, much of what you are asking about does not have a "quick and easy" answer. Optimizing relational databases is a big subject and takes a long time to study and understand. A lot of what you do will depend on what information you are storing and how you are using it.


I have database that has over 100k records and my website its.. lazy. I think its something with queries and database optimization.
Your queries and how you optimise them probably has a lot to do with it, yes. 100,000 records is actually "not that big" for a database. If your DB is designed and used well, sheer size shouldn't be causing problems until you get into the millions-of-records range.


It is better to use two or three database for information? or single? What about queries, its better to recieved data from one connection or make multiply connection with multiply queries?
I depends on the information you are storing and how you need to access it. For most applications, a single database and connection is the best solution.

Note, there is a big difference between "queries" and "connections" (your question seems to mix the two things):

A connection is the communication link between your application (e.g., php script) and the database server. There is usually no need for more than one connection unless you need to access a second database, or you need to access a database as a different user (e.g., with different DB permissions).

A query is another word for an SQL "statement" (or "command"). (Sometimes, it can even refer to several statements that are sent over the DB connection all at once.) A typical application could have just a few queries, or many - it depends on what needs to be done. In general, if you can reduce the number of queries your application uses, you can increase responsiveness. Keep in mind, however, that there are still many other factors: five well-optimized queries will still perform better than one huge, ugly, inefficient query, for example.


What about computer, because maybe if I buy new computer it would go alot smoother, and is there some better optimization commands to find data?
This is very likely not a part of your problem. As long as you have a decent machine (e.g., enough storage and memory), buying new equipment should be an absolute last resort. If you put a badly designed database on a supercomputer, it will run better than it did, but it still won't run well.

If you have more specific questions, please feel free to ask. You would probably need to share your database schema and details about how you use it.

[djr33]

Adrian's answer covers almost everything I would have said (and more!), but I want to add two things:


This is very likely not a part of your problem. As long as you have a decent machine (e.g., enough storage and memory), buying new equipment should be an absolute last resort. If you put a badly designed database on a supercomputer, it will run better than it did, but it still won't run well.Buying a new machine would only make sense if you are at the "millions of entries" level described above. A new machine will speed up the process linearly, but what you're describing sounds like an exponential problem.



A lot of what you do will depend on what information you are storing and how you are using it.
...100,000 records is actually "not that big" for a database. If your DB is designed and used well, sheer size shouldn't be causing problems until you get into the millions-of-records range.It really does depend on the operations. For most normal DB operations, that's true. For some, it might be much harder. For example, if you do full-text searching through long chunks of text, that will take a lot longer. Are you doing anything particularly complicated?



Also, how slow is the website?