MySQL Storage Engine

--0015174483688f467204820fb8bb
Content-Type: text/plain; charset=ISO-8859-1

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

--0015174483688f467204820fb8bb--

Re: MySQL Storage Engine

--0016361e7c1425a7c204820fe894
Content-Type: text/plain; charset=ISO-8859-1

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 > 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
>

--0016361e7c1425a7c204820fe894--

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 +0000, 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=gcdmg-mysql-2@m.gmane.org

Re: MySQL Storage Engine

--00151747af9e758c3d048210071a
Content-Type: text/plain; charset=ISO-8859-1

Hi

How do you mean "executed on slave" ?

Neil

On Thu, Mar 18, 2010 at 9:32 AM, Krishna Chandra Prajapati <
prajapatikc@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.tompkins@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
>>
>
>

--00151747af9e758c3d048210071a--

Re: MySQL Storage Engine

--0016e6469214b1d28404821028e8
Content-Type: text/plain; charset=ISO-8859-1

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.htm l

_Krishna

On Thu, Mar 18, 2010 at 3:11 PM, Tompkins Neil > wrote:

> Hi
>
> How do you mean "executed on slave" ?
>
> Neil
>
>
> On Thu, Mar 18, 2010 at 9:32 AM, Krishna Chandra Prajapati <
> prajapatikc@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.tompkins@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
>>>
>>
>>
>

--0016e6469214b1d28404821028e8--

Re: MySQL Storage Engine

--0015174767ae9fa915048210bb3d
Content-Type: text/plain; charset=ISO-8859-1

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 <
prajapatikc@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.htm l
>
> _Krishna
>
>
> On Thu, Mar 18, 2010 at 3:11 PM, Tompkins Neil <
> neil.tompkins@googlemail.com> wrote:
>
>> Hi
>>
>> How do you mean "executed on slave" ?
>>
>> Neil
>>
>>
>> On Thu, Mar 18, 2010 at 9:32 AM, Krishna Chandra Prajapati <
>> prajapatikc@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.tompkins@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
>>>>
>>>
>>>
>>
>

--0015174767ae9fa915048210bb3d--

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=gcdmg-mysql-2@m.gmane.org

Re: MySQL Storage Engine

--0015173fe48435606b048241e305
Content-Type: text/plain; charset=ISO-8859-1

Thanks for all the responses and useful information.

Cheers
Neil

On Thu, Mar 18, 2010 at 6:35 PM, mos 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.tompkins@googlemail. com
>
>

--0015173fe48435606b048241e305--