super slow queries

[monkeytooth]

Ok so I have a single mySQL database on a dedicated server hosted through hostgator..

 

In this database I have 1 table that is a very large table it has well over 900,000 rows on it and we anticipate another 8.1 million records to be added within another 6 months and from there expect the table to grow regularly. Right now my issue is and can only foresee it getting worse as the size gets larger. Is that my queries are running slower and slower. Sometimes even resulting in the DB crashing. So what I am trying to figure out is how do I speed this up. I've tried 101 ways to query the DB to speed it up and can't get it to load 10 results 10.. in less than 30 seconds. I know some of my issue is the fact that I need distinct results or unique ones rather. As there are several duplicate entries but I only need to pull out one of each for this particular function which is a search.

 

So that said is there any advice one can give me to speed up my query results. I would offer up a sample of my current queries but its just your common query using distinct.

[petroz]

Have you tried caching?

[mikosiko]

the first things that I will do:

- Review in detail the data model to be sure that it is normalized and do no present design problems.

- Review that my model/tables have the appropriated indexes orientated to help my query.

- Review and Analyze the selects that I will use to be sure that are efficiently written (having a good data model behind will help).

 

- After complete the 3 previous steps (that will probably solve the majority of the problems) I will explore things like caching, table partitioning or other methodologies to improve performance.