optimising for 100000 entries

Question:

How can you optimise MySQL for 100000 entires?

Just running OSCemmerce and it is slow to pull up a who catalogue.

--
Member - Liberal International This is doctor@nl2k.ab.ca Ici doctor@nl2k.ab.ca
God, Queen and country! Never Satan President Republic! Beware AntiChrist rising!
https://www.fullyfollow.me/rootnl2k
Ontario, Nfld, and Manitoba boot the extremists out and vote Liberal!

--
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: optimising for 100000 entries

--20cf3054a095603a6404ace6ae61
Content-Type: text/plain; charset=ISO-8859-1

Dr. Doctor,
What kind of 100000 entries? Is it insert,update delete etc.

regards
anandkl

On Wed, Sep 14, 2011 at 6:30 PM, The Doctor wrote:

> Question:
>
> How can you optimise MySQL for 100000 entires?
>
> Just running OSCemmerce and it is slow to pull up a who catalogue.
>
> --
> Member - Liberal International This is doctor@nl2k.ab.ca Ici
> doctor@nl2k.ab.ca
> God, Queen and country! Never Satan President Republic! Beware AntiChrist
> rising!
> https://www.fullyfollow.me/rootnl2k
> Ontario, Nfld, and Manitoba boot the extremists out and vote Liberal!
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

--20cf3054a095603a6404ace6ae61--

Re: optimising for 100000 entries

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

Forgive my bluntness, but IMO it is silly to attempt to retrieve a 100,000
rows, except for reporting purposes, and in that case, said reports ought to
run against a replica, not the OLTP instance.

Far better, IMO, is to present (in the UI) an alphabet as buttons, plus a
textbox for refinements. The alphabet buttons cause the recordSource to
change to something like "SELECT * FROM Clients WHERE ClientName LIKE 'A*'.
Click the B button and the RecordSource changes to "SELECT * FROM Clients
WHERE ClientName LIKE 'B*'. IMO, such an interface gives the user all the
power she needs, and costs the system as little as possible.

To accomplish this, all you need is a sproc that accepts one parameter, that
being the letter corresponding to the letter-button the user pressed.

I have implemented exactly this solution on a table with only half the
number of rows you cite, but it works beautifully and it is quick as
lightning.

HTH,
Arthur

On Wed, Sep 14, 2011 at 9:24 AM, Ananda Kumar wrote:

> Dr. Doctor,
> What kind of 100000 entries? Is it insert,update delete etc.
>
> regards
> anandkl
>
> On Wed, Sep 14, 2011 at 6:30 PM, The Doctor > >wrote:
>
> > Question:
> >
> > How can you optimise MySQL for 100000 entires?
> >
> > Just running OSCemmerce and it is slow to pull up a who catalogue.
> >
> > --
> > Member - Liberal International This is doctor@nl2k.ab.ca Ici
> > doctor@nl2k.ab.ca
> > God, Queen and country! Never Satan President Republic! Beware AntiChrist
> > rising!
> > https://www.fullyfollow.me/rootnl2k
> > Ontario, Nfld, and Manitoba boot the extremists out and vote Liberal!
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
> >
> >
>

--0016e6470ef88c6cce04ace6ff04--

Re: optimising for 100000 entries

--20cf303ea608301af704ace92020
Content-Type: text/plain; charset=ISO-8859-1

So,
You want to have 100,000 buttons for 100,000 entries or just have one filter
column, which allows you to specify any type of "WHERE CONDITION"

regards
anandkl

On Wed, Sep 14, 2011 at 7:17 PM, Arthur Fuller wrote:

> Forgive my bluntness, but IMO it is silly to attempt to retrieve a 100,000
> rows, except for reporting purposes, and in that case, said reports ought to
> run against a replica, not the OLTP instance.
>
> Far better, IMO, is to present (in the UI) an alphabet as buttons, plus a
> textbox for refinements. The alphabet buttons cause the recordSource to
> change to something like "SELECT * FROM Clients WHERE ClientName LIKE 'A*'.
> Click the B button and the RecordSource changes to "SELECT * FROM Clients
> WHERE ClientName LIKE 'B*'. IMO, such an interface gives the user all the
> power she needs, and costs the system as little as possible.
>
> To accomplish this, all you need is a sproc that accepts one parameter,
> that being the letter corresponding to the letter-button the user pressed.
>
> I have implemented exactly this solution on a table with only half the
> number of rows you cite, but it works beautifully and it is quick as
> lightning.
>
> HTH,
> Arthur
>
>
> On Wed, Sep 14, 2011 at 9:24 AM, Ananda Kumar wrote:
>
>> Dr. Doctor,
>> What kind of 100000 entries? Is it insert,update delete etc.
>>
>> regards
>> anandkl
>>
>> On Wed, Sep 14, 2011 at 6:30 PM, The Doctor >> >wrote:
>>
>> > Question:
>> >
>> > How can you optimise MySQL for 100000 entires?
>> >
>> > Just running OSCemmerce and it is slow to pull up a who catalogue.
>> >
>> > --
>> > Member - Liberal International This is doctor@nl2k.ab.ca Ici
>> > doctor@nl2k.ab.ca
>> > God, Queen and country! Never Satan President Republic! Beware
>> AntiChrist
>> > rising!
>> > https://www.fullyfollow.me/rootnl2k
>> > Ontario, Nfld, and Manitoba boot the extremists out and vote Liberal!
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>> >
>> >
>>
>
>

--20cf303ea608301af704ace92020--

Re: optimising for 100000 entries

--20cf30780f30554b3804acea2cab
Content-Type: text/plain; charset=ISO-8859-1

Call me stupid, but IMO this is just plain Bad Design. Maybe your masters
are making you want to deliver this, but it's frankly insane. Go instead
with the Sally Rand Principle: show them just enough to pique their
interest. Given that you've narrowed it down to the WHERE clause, try again.
WHERE LIKE 'xyz', and narrow your list from there. Nobody wants
to view 100K buttons or choices or whatever. The most I want to see is about
20, at any given take.

On Wed, Sep 14, 2011 at 12:19 PM, Ananda Kumar wrote:

> So,
> You want to have 100,000 buttons for 100,000 entries or just have one
> filter column, which allows you to specify any type of "WHERE CONDITION"
>
> regards
> anandkl
>
>
>

--20cf30780f30554b3804acea2cab--

Re: optimising for 100000 entries

On Wed, Sep 14, 2011 at 09:49:34PM +0530, Ananda Kumar wrote:
> So,
> You want to have 100,000 buttons for 100,000 entries or just have one filter
> column, which allows you to specify any type of "WHERE CONDITION"
>
> regards
> anandkl
>
> On Wed, Sep 14, 2011 at 7:17 PM, Arthur Fuller wrote:
>
> > Forgive my bluntness, but IMO it is silly to attempt to retrieve a 100,000
> > rows, except for reporting purposes, and in that case, said reports ought to
> > run against a replica, not the OLTP instance.
> >
> > Far better, IMO, is to present (in the UI) an alphabet as buttons, plus a
> > textbox for refinements. The alphabet buttons cause the recordSource to
> > change to something like "SELECT * FROM Clients WHERE ClientName LIKE 'A*'.
> > Click the B button and the RecordSource changes to "SELECT * FROM Clients
> > WHERE ClientName LIKE 'B*'. IMO, such an interface gives the user all the
> > power she needs, and costs the system as little as possible.
> >
> > To accomplish this, all you need is a sproc that accepts one parameter,
> > that being the letter corresponding to the letter-button the user pressed.
> >
> > I have implemented exactly this solution on a table with only half the
> > number of rows you cite, but it works beautifully and it is quick as
> > lightning.
> >
> > HTH,
> > Arthur

Arthur,

this is exactly what comes to mind.

I am wonder what needs to be adjusted in osCommerce for this to work.

> >
> >
> > On Wed, Sep 14, 2011 at 9:24 AM, Ananda Kumar wrote:
> >
> >> Dr. Doctor,
> >> What kind of 100000 entries? Is it insert,update delete etc.
> >>
> >> regards
> >> anandkl
> >>
> >> On Wed, Sep 14, 2011 at 6:30 PM, The Doctor > >> >wrote:
> >>
> >> > Question:
> >> >
> >> > How can you optimise MySQL for 100000 entires?
> >> >
> >> > Just running OSCemmerce and it is slow to pull up a who catalogue.
> >> >
> >> > --
> >> > Member - Liberal International This is doctor@nl2k.ab.ca Ici
> >> > doctor@nl2k.ab.ca
> >> > God, Queen and country! Never Satan President Republic! Beware
> >> AntiChrist
> >> > rising!
> >> > https://www.fullyfollow.me/rootnl2k
> >> > Ontario, Nfld, and Manitoba boot the extremists out and vote Liberal!
> >> >
> >> > --
> >> > MySQL General Mailing List
> >> > For list archives: http://lists.mysql.com/mysql
> >> > To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
> >> >
> >> >
> >>
> >
> >

--
Member - Liberal International This is doctor@nl2k.ab.ca Ici doctor@nl2k.ab.ca
God, Queen and country! Never Satan President Republic! Beware AntiChrist rising!
https://www.fullyfollow.me/rootnl2k
Ontario, Nfld, and Manitoba boot the extremists out and vote Liberal!

--
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: optimising for 100000 entries

On Thu, Sep 15, 2011 at 08:55:16AM -0400, Shawn Green (MySQL) wrote:
> On 9/14/2011 15:26, The Doctor wrote:
>> On Wed, Sep 14, 2011 at 09:49:34PM +0530, Ananda Kumar wrote:
>>> So,
>>> You want to have 100,000 buttons for 100,000 entries or just have one filter
>>> column, which allows you to specify any type of "WHERE CONDITION"
>>>
>>> regards
>>> anandkl
>>>
>>> On Wed, Sep 14, 2011 at 7:17 PM, Arthur Fullerwrote:
>>>
>>>> Forgive my bluntness, but IMO it is silly to attempt to retrieve a 100,000
>>>> rows, except for reporting purposes, and in that case, said reports ought to
>>>> run against a replica, not the OLTP instance.
>>>>
>>>> Far better, IMO, is to present (in the UI) an alphabet as buttons, plus a
>>>> textbox for refinements. The alphabet buttons cause the recordSource to
>>>> change to something like "SELECT * FROM Clients WHERE ClientName LIKE 'A*'.
>>>> Click the B button and the RecordSource changes to "SELECT * FROM Clients
>>>> WHERE ClientName LIKE 'B*'. IMO, such an interface gives the user all the
>>>> power she needs, and costs the system as little as possible.
>>>>
>>>> To accomplish this, all you need is a sproc that accepts one parameter,
>>>> that being the letter corresponding to the letter-button the user pressed.
>>>>
>>>> I have implemented exactly this solution on a table with only half the
>>>> number of rows you cite, but it works beautifully and it is quick as
>>>> lightning.
>>>>
>>>> HTH,
>>>> Arthur
>>
>> Arthur,
>>
>> this is exactly what comes to mind.
>>
>> I am wonder what needs to be adjusted in osCommerce for this to work.
>>
>
> I am still confused by your question. Most modern databases (even those
> that are not client-server capable) don't even break a sweat at handling
> only 100K rows of data. It is the types of queries you write and how much
> data you are attempting to move at any one time that are the most likely
> reasons for poor performance.
>
> Please clarify what you want to fix when you say "optimise MySQL for 100000
> entires". Even with the minimal settings on a low-powered laptop, I would
> have no qualms about loading any version of MySQL produced in the last 10
> years with a million rows of data and using it for personal research. Of
> course, there are things I could (and would) configure to help MySQL use
> it's host system more efficiently. All of that is covered in the chapter in
> the operating manual called "Optimization". Pick the link below that
> matches the version you are using for more information:
> http://dev.mysql.com/doc/refman/5.0/en/optimization.html
> http://dev.mysql.com/doc/refman/5.1/en/optimization.html
> http://dev.mysql.com/doc/refman/5.5/en/optimization.html
>
> Perhaps if you could tell us what you are trying to do we could suggest
> ways for doing it better?
>

Clarification:

I have 100000 **products** loaded into the shopping cart.

FRom there is slow to bring up the shopping cart.

Check http://www.nk.ca/~aboo/racing/osc4/catalog/

to see what is happening.

> --
> Shawn Green
> MySQL Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN

--
Member - Liberal International This is doctor@nl2k.ab.ca Ici doctor@nl2k.ab.ca
God, Queen and country! Never Satan President Republic! Beware AntiChrist rising!
https://www.fullyfollow.me/rootnl2k
Ontario, Nfld, and Manitoba boot the extremists out and vote Liberal!

--
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: optimising for 100000 entries

On Thu, September 15, 2011 11:27, The Doctor wrote:
> On Thu, Sep 15, 2011 at 08:55:16AM -0400, Shawn Green (MySQL) wrote:
>> On 9/14/2011 15:26, The Doctor wrote:
>>> On Wed, Sep 14, 2011 at 09:49:34PM +0530, Ananda Kumar wrote:
>>>> So,
>>>> You want to have 100,000 buttons for 100,000 entries or just have one
>>>> filter
>>>> column, which allows you to specify any type of "WHERE CONDITION"
>>>>
>>>> regards
>>>> anandkl
>>>>
>>>> On Wed, Sep 14, 2011 at 7:17 PM, Arthur
>>>> Fullerwrote:
>>>>
>>>>> Forgive my bluntness, but IMO it is silly to attempt to retrieve a
>>>>> 100,000
>>>>> rows, except for reporting purposes, and in that case, said reports
>>>>> ought to
>>>>> run against a replica, not the OLTP instance.
>>>>>
>>>>> Far better, IMO, is to present (in the UI) an alphabet as buttons,
>>>>> plus a
>>>>> textbox for refinements. The alphabet buttons cause the recordSource
>>>>> to
>>>>> change to something like "SELECT * FROM Clients WHERE ClientName LIKE
>>>>> 'A*'.
>>>>> Click the B button and the RecordSource changes to "SELECT * FROM
>>>>> Clients
>>>>> WHERE ClientName LIKE 'B*'. IMO, such an interface gives the user all
>>>>> the
>>>>> power she needs, and costs the system as little as possible.
>>>>>
>>>>> To accomplish this, all you need is a sproc that accepts one
>>>>> parameter,
>>>>> that being the letter corresponding to the letter-button the user
>>>>> pressed.
>>>>>
>>>>> I have implemented exactly this solution on a table with only half
>>>>> the
>>>>> number of rows you cite, but it works beautifully and it is quick as
>>>>> lightning.
>>>>>
>>>>> HTH,
>>>>> Arthur
>>>
>>> Arthur,
>>>
>>> this is exactly what comes to mind.
>>>
>>> I am wonder what needs to be adjusted in osCommerce for this to work.
>>>
>>
>> I am still confused by your question. Most modern databases (even those
>> that are not client-server capable) don't even break a sweat at handling
>> only 100K rows of data. It is the types of queries you write and how
>> much
>> data you are attempting to move at any one time that are the most likely
>> reasons for poor performance.
>>
>> Please clarify what you want to fix when you say "optimise MySQL for
>> 100000
>> entires". Even with the minimal settings on a low-powered laptop, I
>> would
>> have no qualms about loading any version of MySQL produced in the last
>> 10
>> years with a million rows of data and using it for personal research. Of
>> course, there are things I could (and would) configure to help MySQL use
>> it's host system more efficiently. All of that is covered in the chapter
>> in
>> the operating manual called "Optimization". Pick the link below that
>> matches the version you are using for more information:
>> http://dev.mysql.com/doc/refman/5.0/en/optimization.html
>> http://dev.mysql.com/doc/refman/5.1/en/optimization.html
>> http://dev.mysql.com/doc/refman/5.5/en/optimization.html
>>
>> Perhaps if you could tell us what you are trying to do we could suggest
>> ways for doing it better?
>>
>
> Clarification:
>
> I have 100000 **products** loaded into the shopping cart.
>
> FRom there is slow to bring up the shopping cart.
>
> Check http://www.nk.ca/~aboo/racing/osc4/catalog/
>
> to see what is happening.
OK, this is a catalog not a shopping cart.
I think you need to turn off buffering so that the web server sends stuff
out as it gets it. It appears to be waiting for the full page to be built
before it sends anything. Its a php system so a PHP expert should chime
in with how to do this.

I suspect the category system which is listing the number of products on
the left is taking a lot of the time.
How do you link your products to your categories? I suspect you will need
to add a column in the category table to list the number of products so
you are not doing a select count(*) Products under Category X each time.
If your select to list the categories is also doing the count then you
might want to try separating that out (two select's). Without your table
structure I'm shooting in the dark a bit here.

Just a few thoughts. Luck.

The shopping cart is not the issue, the category list on the left is, I
think.


-------

William R. Mussatto, Senior Systems Engineer
http://www.csz.com
Ph. 909-920-9154 ext 101
Direct: 909-962-8547
____________________________________________________________ __________
CONFIDENTIALITY NOTICE:
This communication, including attachments, is for the exclusive use of the
person or entity to which it is addressed and may contain confidential,
proprietary and/or privileged information. Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient
is prohibited. If you received this by mistake, please contact the sender
immediately.


--
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: optimising for 100000 entries

Doc,
When the user clicks on the Category on the left, it is not
changing the URL. If I change the url manually to "Path=2" etc. and
press enter, the page loads correctly with that category. So it looks
like your problem is the PHP code not executing the SQL query at all
or it is not requesting a page refresh with the new results.

Mike

At 01:27 PM 9/15/2011, you wrote:
>On Thu, Sep 15, 2011 at 08:55:16AM -0400, Shawn Green (MySQL) wrote:
> > On 9/14/2011 15:26, The Doctor wrote:
> >> On Wed, Sep 14, 2011 at 09:49:34PM +0530, Ananda Kumar wrote:
> >>> So,
> >>> You want to have 100,000 buttons for 100,000 entries or just
> have one filter
> >>> column, which allows you to specify any type of "WHERE CONDITION"
> >>>
> >>> regards
> >>> anandkl
> >>>
> >>> On Wed, Sep 14, 2011 at 7:17 PM, Arthur
> Fullerwrote:
> >>>
> >>>> Forgive my bluntness, but IMO it is silly to attempt to
> retrieve a 100,000
> >>>> rows, except for reporting purposes, and in that case, said
> reports ought to
> >>>> run against a replica, not the OLTP instance.
> >>>>
> >>>> Far better, IMO, is to present (in the UI) an alphabet as
> buttons, plus a
> >>>> textbox for refinements. The alphabet buttons cause the recordSource to
> >>>> change to something like "SELECT * FROM Clients WHERE
> ClientName LIKE 'A*'.
> >>>> Click the B button and the RecordSource changes to "SELECT *
> FROM Clients
> >>>> WHERE ClientName LIKE 'B*'. IMO, such an interface gives the
> user all the
> >>>> power she needs, and costs the system as little as possible.
> >>>>
> >>>> To accomplish this, all you need is a sproc that accepts one parameter,
> >>>> that being the letter corresponding to the letter-button the
> user pressed.
> >>>>
> >>>> I have implemented exactly this solution on a table with only half the
> >>>> number of rows you cite, but it works beautifully and it is quick as
> >>>> lightning.
> >>>>
> >>>> HTH,
> >>>> Arthur
> >>
> >> Arthur,
> >>
> >> this is exactly what comes to mind.
> >>
> >> I am wonder what needs to be adjusted in osCommerce for this to work.
> >>
> >
> > I am still confused by your question. Most modern databases (even those
> > that are not client-server capable) don't even break a sweat at handling
> > only 100K rows of data. It is the types of queries you write and how much
> > data you are attempting to move at any one time that are the most likely
> > reasons for poor performance.
> >
> > Please clarify what you want to fix when you say "optimise MySQL
> for 100000
> > entires". Even with the minimal settings on a low-powered laptop, I would
> > have no qualms about loading any version of MySQL produced in the last 10
> > years with a million rows of data and using it for personal research. Of
> > course, there are things I could (and would) configure to help MySQL use
> > it's host system more efficiently. All of that is covered in the
> chapter in
> > the operating manual called "Optimization". Pick the link below that
> > matches the version you are using for more information:
> > http://dev.mysql.com/doc/refman/5.0/en/optimization.html
> > http://dev.mysql.com/doc/refman/5.1/en/optimization.html
> > http://dev.mysql.com/doc/refman/5.5/en/optimization.html
> >
> > Perhaps if you could tell us what you are trying to do we could suggest
> > ways for doing it better?
> >
>
>Clarification:
>
>I have 100000 **products** loaded into the shopping cart.
>
>FRom there is slow to bring up the shopping cart.
>
>Check http://www.nk.ca/~aboo/racing/osc4/catalog/
>
>to see what is happening.
>
> > --
> > Shawn Green
> > MySQL Principal Technical Support Engineer
> > Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> > Office: Blountville, TN
>
>--
>Member - Liberal International This is doctor@nl2k.ab.ca Ici
>doctor@nl2k.ab.ca
>God, Queen and country! Never Satan President Republic! Beware
>AntiChrist rising!
>https://www.fullyfollow.me/rootnl2k
>Ontario, Nfld, and Manitoba boot the extremists out and vote Liberal!
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm


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