Re: MySQL Storage Engine
Thanks for all the responses and useful information. Cheers Neil On Thu, Mar 18, 2010 at 6:35 PM, mos mo...@fastmail.fm wrote: At 04:18 AM 3/18/2010, Tompkins Neil wrote: Hi I'm currently looking to develop an on line web application - which is going to be used by around 200+ concurrent users at any one time. Can you recommend to me the best/preferred MySQL database engine to use for example MyISAM ? Also, in your experiences, do you recommend that SELECT statements containing a number of INNER JOINS should be execute from a Stored procedure or purely from the webpage ? Neil, I would recommend using MyISAM if the queries where 90% reads and less than 10% writes. Since you are running a webpage you will of course need to use parameterized queries to avoid sql injection attacks. Innodb is fine if the table is small enough that it will fit into memory. If you don't have enough ram on your machine then innodb can be quite sluggish. If you need transactions, then Innodb is necessary. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
MySQL Storage Engine
Hi I'm currently looking to develop an on line web application - which is going to be used by around 200+ concurrent users at any one time. Can you recommend to me the best/preferred MySQL database engine to use for example MyISAM ? Also, in your experiences, do you recommend that SELECT statements containing a number of INNER JOINS should be execute from a Stored procedure or purely from the webpage ? Cheers Neil
Re: MySQL Storage Engine
Hi Neil, Recommend : Innodb storage engine Even, i would suggest inner join select query (if report query might takes few seconds) should be executed on slave. Before doing any thing. Plan out all the requirement. Regards, Krishna On Thu, Mar 18, 2010 at 2:48 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm currently looking to develop an on line web application - which is going to be used by around 200+ concurrent users at any one time. Can you recommend to me the best/preferred MySQL database engine to use for example MyISAM ? Also, in your experiences, do you recommend that SELECT statements containing a number of INNER JOINS should be execute from a Stored procedure or purely from the webpage ? Cheers Neil
Re: MySQL Storage Engine
Hi Neil, As Krishna said, Innodb is generally always superior to MyISAM unless you need full-text search (and even if you do there are alternatives to MyISAMs full-text search, which I've often found to be lacking in performance). A poorly optimised query will be slow whether it is called from a procedure or executed directly by the page. I'd advise analysing your joined queries carefully, using the EXPLAIN tool in MySQL, and add appropriate indexes to your tables. Also, if you are developing in PHP, you may wish to look at the memcache module, which can cache function results in memory in PHP. This can often be helpful for storing results for very frequently called queries. On Thu, 2010-03-18 at 09:18 +, Tompkins Neil wrote: Hi I'm currently looking to develop an on line web application - which is going to be used by around 200+ concurrent users at any one time. Can you recommend to me the best/preferred MySQL database engine to use for example MyISAM ? Also, in your experiences, do you recommend that SELECT statements containing a number of INNER JOINS should be execute from a Stored procedure or purely from the webpage ? Cheers Neil -- Ian Simpson System Administrator MyJobGroup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Storage Engine
Hi How do you mean executed on slave ? Neil On Thu, Mar 18, 2010 at 9:32 AM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi Neil, Recommend : Innodb storage engine Even, i would suggest inner join select query (if report query might takes few seconds) should be executed on slave. Before doing any thing. Plan out all the requirement. Regards, Krishna On Thu, Mar 18, 2010 at 2:48 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm currently looking to develop an on line web application - which is going to be used by around 200+ concurrent users at any one time. Can you recommend to me the best/preferred MySQL database engine to use for example MyISAM ? Also, in your experiences, do you recommend that SELECT statements containing a number of INNER JOINS should be execute from a Stored procedure or purely from the webpage ? Cheers Neil
Re: MySQL Storage Engine
Hi Neil, Many times we need to generate reports (weekly, montly, yearly) from the data we have. For detailed reports we have to use joins on many tables. So, it takes time from 2 to 5. So these types of activities must be performed on slave server. If you need reports then you should have master - slave replication. http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html _Krishna On Thu, Mar 18, 2010 at 3:11 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi How do you mean executed on slave ? Neil On Thu, Mar 18, 2010 at 9:32 AM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi Neil, Recommend : Innodb storage engine Even, i would suggest inner join select query (if report query might takes few seconds) should be executed on slave. Before doing any thing. Plan out all the requirement. Regards, Krishna On Thu, Mar 18, 2010 at 2:48 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm currently looking to develop an on line web application - which is going to be used by around 200+ concurrent users at any one time. Can you recommend to me the best/preferred MySQL database engine to use for example MyISAM ? Also, in your experiences, do you recommend that SELECT statements containing a number of INNER JOINS should be execute from a Stored procedure or purely from the webpage ? Cheers Neil
Re: MySQL Storage Engine
Thanks for the useful information. Can you let me know any other things I need to consider ? Regards Neil On Thu, Mar 18, 2010 at 9:50 AM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi Neil, Many times we need to generate reports (weekly, montly, yearly) from the data we have. For detailed reports we have to use joins on many tables. So, it takes time from 2 to 5. So these types of activities must be performed on slave server. If you need reports then you should have master - slave replication. http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html _Krishna On Thu, Mar 18, 2010 at 3:11 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi How do you mean executed on slave ? Neil On Thu, Mar 18, 2010 at 9:32 AM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi Neil, Recommend : Innodb storage engine Even, i would suggest inner join select query (if report query might takes few seconds) should be executed on slave. Before doing any thing. Plan out all the requirement. Regards, Krishna On Thu, Mar 18, 2010 at 2:48 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I'm currently looking to develop an on line web application - which is going to be used by around 200+ concurrent users at any one time. Can you recommend to me the best/preferred MySQL database engine to use for example MyISAM ? Also, in your experiences, do you recommend that SELECT statements containing a number of INNER JOINS should be execute from a Stored procedure or purely from the webpage ? Cheers Neil
Re: MySQL Storage Engine
At 04:18 AM 3/18/2010, Tompkins Neil wrote: Hi I'm currently looking to develop an on line web application - which is going to be used by around 200+ concurrent users at any one time. Can you recommend to me the best/preferred MySQL database engine to use for example MyISAM ? Also, in your experiences, do you recommend that SELECT statements containing a number of INNER JOINS should be execute from a Stored procedure or purely from the webpage ? Neil, I would recommend using MyISAM if the queries where 90% reads and less than 10% writes. Since you are running a webpage you will of course need to use parameterized queries to avoid sql injection attacks. Innodb is fine if the table is small enough that it will fit into memory. If you don't have enough ram on your machine then innodb can be quite sluggish. If you need transactions, then Innodb is necessary. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
