MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 11 2005 3:04pm
Subject:Re: slow query, how can i imporve it?
View as plain text  
YES, I need a LOT more information.  Please provide ALL the information I 
asked for in my previous post (especially questions 1, 2, and 3). To 
compare with my "automobile" analogy: You told me that your auto is towing 
a lot of identical trailers and that if you use a different vehicle on a 
different road, you can drive faster pulling the same load. Your 
information is useful as additional information but not useful to answer 
your specific question.

If you want specific help about a specific query, I have to have the 
information that is specific to your query.  Comparing performance with 
another engine is not descriptive of the issues you are having. This is 
especially true for the query you give _as an example_ because COUNT(*) is 
handled very differently in the two database servers you compared. InnoDb 
uses versioning locks on it's records, that makes it practically 
impossible to determine exactly how many records are available to any user 
at any one time. This improves concurrency but makes COUNT(*) hard to 
compute quickly. How InnoDB estimates COUNT(*) is by taking the average of 
10 random "dives" through the index tree.

Please respond with the information that ANYONE (not just I) would need in 
order to answer your questions.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Shailendra Soni <sonishailendra02@stripped> wrote on 02/11/2005 09:35:53 
AM:

> HI,
> 
> i give some more information about my application.
> 
> 1) i have 41 million records , and this records are in 10 tables.so
> each table contains arrounds 4 million records.
> 2) Each table contains same columns definition . Total column is 61
> and total number of the indexes column is 6.ok
> 3)now i fired the query like "select count(*) from tablename where 
........."
> in where clause having allmost all columns.
> 4) that query is fired on 10 tables from servlet with 10 threades .okk
> when i execute , i got the result after 6 to 7 minute.
> 
> upto that i think you get my point.
> 
> now i want to that result will come in 2 to 3 minute.
> is this possible in Mysql?
> 
> also i have restored all my tables in mssql and 
> then mssql give me result in 2 to 3 minute.
> but i can't my whole database shift to mssql.
> 
> so
> can you have some idea that how can i speed up my query ? 
> 
> if you want to more description then let me know.
> 
> Thank you
> Shailendra
> 
> 
> On Thu, 10 Feb 2005 11:06:04 -0500, SGreen@stripped 
> <SGreen@stripped> wrote:
> > 
> > See below.... 
> > 
> > Shailendra Soni <sonishailendra02@stripped> wrote on 02/10/2005 
01:43:18
> > AM:
> > 
> > > Thank ,
> > > But i can't create multipal index it will not useful for my tabels.
> > > 
> > > I tryed to set GLOBAL keycache1.key_buffer_size = 128*1024
> > > 
> > > but it gives error that "unknown system varible ' keycache1' ".
> > > can you tell me that is this useful for my problem? and
> > > if yes how it is work? and how can i solve this error.
> > > 
> > > Thanks again
> > > reply soon 
> > > 
> > > Regards:
> > > Shailendra
> > > 
> > 
> > I do not recognize that command either. Where did you find it and how 
was it
> > related to improving query performance? 
> > 
> > This situation is analogous to you saying to me "My car is slow, how 
do I
> > make it go faster?". I know nothing about your table structures, your
> > indexes, your query, or the issue itself (exactly how slow is it? how 
fast
> > would you like it to be?). If you really need help with a query, 
please
> > respond with all of the following information: 
> > 
> > 1) The text of the actual query 
> > 2) The results of an EXPLAIN on that query 
> > 3) The results of SHOW CREATE TABLE xxxxx\G for each table used in the
> > query. 
> > 4) A description of why this query is not meeting your needs and what 
needs
> > you would like it to meet. 
> > 
> > Once I have all of that background information, either I or someone 
else on
> > the list will be able to help you with this issue. Do not forget to 
CC: the
> > list with your responses. 
> > 
> > 
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine 
> > 
> > > 
> > > 
> > > On Wed, 9 Feb 2005 10:02:49 -0500, SGreen@stripped <SGreen@unimin.
> > > com> wrote:
> > > > 
> > > > 
> > > > Shailendra Soni <sonishailendra02@stripped> wrote on
> 02/09/2005
> > 08:28:36
> > > > AM:
> > > > 
> > > > 
> > > > > Hi,
> > > > > 
> > > > > I have a question regarding speed of the query.
> > > > > In my application i am useing Mysql 4.0.20a-nt.
> > > > > I have 10 tables and each table contains 4000000 records
> > > > > and also 61 columns. I already created indexs on six column 
which are
> > > > > important for me.
> > > > > 
> > > > > i fired the query on tables through servlet(thread).
> > > > > I fired same query on all tables on same time, but it has take 
time to
> > > > > getting result . allmost
> > > > > 7 to 10 minute .
> > > > > 
> > > > > so please tell me 
> > > > > how can i imporve speed of the Mysql or query?
> > > > > 
> > > > > so it will take less time !
> > > > > 
> > > > > Thanks 
> > > > > Shailendra
> > > > > 
> > > > 
> > > > Have you tried looking at this for ideas, too? 
> > > > http://dev.mysql.com/doc/mysql/en/query-speed.html 
> > > > 
> > > > Most of us start with an EXPLAIN of the query and work from there 
(see
> > > > suggested reading). Check your table structures and, if the 
frequency of
> > > > this query justifies it, an appropriate multi-column index (not 
multiple
> > > > single-column indexes). 
> > > > 
> > > > Shawn Green
> > > > Database Administrator
> > > > Unimin Corporation - Spruce Pine
> > 
> >
Thread
slow query, how can i imporve it?Shailendra Soni9 Feb
  • Re: slow query, how can i imporve it?SGreen9 Feb
    • Warning: V3 DSA signature: NOKEY, key ID 5072e1f5Chuck Herrick9 Feb
RE: slow query, how can i imporve it?Jay Blanchard9 Feb
Re: slow query, how can i imporve it?SGreen10 Feb
  • Re: slow query, how can i imporve it?Shailendra Soni11 Feb
    • Re: slow query, how can i imporve it?SGreen11 Feb
      • Re: slow query, how can i imporve it?SGreen11 Feb
        • Re: slow query, how can i imporve it?Chris Elsworth12 Feb
          • Re: slow query, how can i imporve it?Michael Stassen12 Feb
Re: slow query, how can i imporve it?Heikki Tuuri13 Feb