I am using shared hosting with dreamhost. The database has 50 tables, each has millions of records. I indexed the tables. Some needed queries are super slow (they take 50 seconds)... Any recommendations ? Should I get dedicated server ? If I get dedicated server, what Ram should I get ? Any help is appreciated? Thanks
normally 50 tables you will do well in the shared hosting environment just ask for dedicated mysql server will help you
did you run the explain plan of your statements? EXPLAIN *QUERY* It will return you how mysql resolves the queries. If it needs temporary tables on RAM or on DISK, better on RAM than physically... Same thing for the sorts, etc... Do you have the control on the server settings? Some huge databases like yours needs to be tuned or dedicated... If ALL of your tables are filled by million rows like you said, you probably need to move this db on a dedicated server... hope that helps Bruno
you said 50 tables and each one has millions row. so total is 50 millions. you need minimum 4 GB RAM on dedicated server and big disk space for virtual memory. of course, if you can have more RAM is better. I am just telling you minimum 4 GB. about slow query; I don't know nothing about the structure of databases and I think you have to also take look cache of db.. hope that helps.
you shouldnt need a dedicated server. Just hosting on a less popular server. One where there are at the max 50 clients I sell hosting and i have around 50 clients and i have a crapload of tables and entries and mine works fine.