Re: Multiple Table-Model, Multiple DB
No worries. Sounds like your database needs tuning (indexes etc). I'd look there first. I'd also examine what data you are querying to make sure you are not retrieving unnecessary data. If you are going to go the Cake route (which I'd highly recommend) then I'd encourage you to look at these sections of the book for starters: http://book.cakephp.org/view/1039/Associations-Linking-Models-Together http://book.cakephp.org/view/1323/Containable http://book.cakephp.org/view/1017/Retrieving-Your-Data Jeremy Burns jeremybu...@me.com (Skype) +44 208 123 3822 (jeremy_burns) (m) +44 7973 481949 (h) +44 208 530 7573 On 31 Mar 2010, at 11:07, cupidoly wrote: > Jeremy Burns, > > I truly salute you for such a detailed explanation. Actually, I always > prefer RDBMS. I just wanted to try a new concept. I have already > mentioned that this is just a concept, nothing into implementation. We > are just brainstorming. Our boss likes any new idea, let it be > bullshit. He later decides what suits the best. > > I personally think sometimes it's better to make mistakes and learn. > > My concept I talked about, actually came from another project that I'm > maintaining right now. The project was build few months ago by someone > else, and it has few thousands of members now. Gradually as the > members grew, their data about their activities grew exponentially. > Now the activities tables has few millions of rows. Initially the site > was giving very good performance, but now, its super slow. Our team > found out that mysql is the bottleneck. Now we have decided to build > our projects in-house rather than outsourcing. So we're just > brainstorming what could be a good solution for us. I know we can use > RDBMS with MySQL Cluster, or even NoSQL with MongoDB or many other > solutions are out there. Initially, I just thought this will be a > cheap option to go with. Later on, I just understood my mistake. > > Thank you very much for your time. > > NB - Please forgive my poor english, as i'm not a native english > speaker. > > Check out the new CakePHP Questions site http://cakeqs.org and help others > with their CakePHP related questions. > > You received this message because you are subscribed to the Google Groups > "CakePHP" group. > To post to this group, send email to cake-php@googlegroups.com > To unsubscribe from this group, send email to > cake-php+unsubscr...@googlegroups.com For more options, visit this group at > http://groups.google.com/group/cake-php?hl=en > > To unsubscribe, reply using "remove me" as the subject. Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en To unsubscribe from this group, send email to cake-php+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
Re: Multiple Table-Model, Multiple DB
Jeremy Burns, I truly salute you for such a detailed explanation. Actually, I always prefer RDBMS. I just wanted to try a new concept. I have already mentioned that this is just a concept, nothing into implementation. We are just brainstorming. Our boss likes any new idea, let it be bullshit. He later decides what suits the best. I personally think sometimes it's better to make mistakes and learn. My concept I talked about, actually came from another project that I'm maintaining right now. The project was build few months ago by someone else, and it has few thousands of members now. Gradually as the members grew, their data about their activities grew exponentially. Now the activities tables has few millions of rows. Initially the site was giving very good performance, but now, its super slow. Our team found out that mysql is the bottleneck. Now we have decided to build our projects in-house rather than outsourcing. So we're just brainstorming what could be a good solution for us. I know we can use RDBMS with MySQL Cluster, or even NoSQL with MongoDB or many other solutions are out there. Initially, I just thought this will be a cheap option to go with. Later on, I just understood my mistake. Thank you very much for your time. NB - Please forgive my poor english, as i'm not a native english speaker. Check out the new CakePHP Questions site http://cakeqs.org and help others with their CakePHP related questions. You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to cake-php@googlegroups.com To unsubscribe from this group, send email to cake-php+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/cake-php?hl=en To unsubscribe, reply using "remove me" as the subject.
Re: Multiple Table-Model, Multiple DB
Golly this is a long answer and I'm sorry if it sounds like an unhelpful rant. But I feel I need to make you rethink your strategy. Forgive me for saying this, but it does rather sound as if you are reinventing the wheel and giving yourself a bunch of challenges along the way. A well set up MySQL db can handle millions of rows with little problem - especially if it is built correctly. Read this for starters: http://articles.classoutfit.com/2010/02/cakephp-basic-database-table-tuning-tips/ Cake *can* do more or less anything you want it to. To make your schema work you'd need to create a model for each table (so a model for member_us_ny, member_us_ca etc). What happens if you decide to bring in members from, say China? You'd have to add a new table and model at that time - which is not scalable, not ideal and not very easy to maintain. I know you are going to say "But we aren't planning on going to China" - but that will just demonstrate to me that you haven't thought about the future at all. In a well thought out relational database you just add a row in the countries table for the new country - and it's job done. Your way you are creating a design change - that breaks the rules. What will you do with your existing data if you decide to change your geographical spread? And how are you going to build and maintain any sort of relationships between these abstract tables - whether through tradition RI or at a query level? How are you going build cohesive indexes? How do you tie together all of your premium members, or your female members, or your members who haven't paid? Have you thought about how you would answer questions like "How many members have we got?", or "How many members do we have in Europe?", or "How many of these products did we sell in America?" or "How many products did we sell to premium members who are not in America?"? You are going to have to do a bunch of queries across a bunch of tables (even servers), get the information together and then do a whole load of processing and throw away the data you extracted and didn't need after all. All you have done is move (and grown) the problem, not solved it. But assuming that you want to go this way... The model contains the rules and logic for accessing the data, validating data before it's saved, saving it and so on. If you have more than one model for essentially the same data structure you are going to have to keep that logic synchronised if you decide to change your business rules. You'd then need controllers and views for the models - you could make these dynamic and instruct the controller to use a certain model based on the underlying table. You could even make the model dynamic by telling it which table to use at run time. But doing so would disable or impede Cake's ability to cache the model structure because it would be subject to change. And spreading different tables across different servers sounds crazy. A simple "products sold in these countries" query would involve: working out which member tables you need to query (which members are the American ones?), working out which product tables you need to query, working out which sales tables to query, working out which servers the tables are on, working out which queries you need to run, running the stack of queries, progressively storing the returned results somewhere (memory? cache? another table? another database?), combining the results, parsing the dataset and presenting the answer. Compare that with a single query to a single database on a single server - I know which I'd rather do. As far as caching is concerned, you aren't really solving or reducing the problem by changing the way you store the data. If your eventual query results in, say, 100,000 rows, then your web server will still need to handle that volume (although there are better ways of doing this). In fact, your way, you might need to bring back 700,000 rows just to find out you only needed 100,000 - or just to return a single row (see my sample queries earlier - "We sold x products in America last year"). You say you want MySQL to do the job - then I'd let it get on with it unimpeded. In my opinion, you are far better off building a pure, well indexed and well set up relational database with all your similar data in single, indexed, related tables, rather than abstracting that work and trying to manage it in PHP. All you are doing is shifting the work, adding complexity and exposing yourself to risk. Then I'd look at load balancing and hardware and reverse proxies to handle the volumes - not your way. You are trying to plough a field with a hammer because hammers are good at hitting nails. I wouldn't want to be the client still paying for design changes in five years time and I wouldn't want to be the developer who walks in after you. Maybe I have misunderstood what you are trying to achieve or your underlying business is going to be so huge and data de
