Re: slow query, how can i imporve it?

[SGreen]

Normally I do not reply to myself but I just realized that in my previous 
response I confused COUNT(*) (which is slow for InnoDB because it always 
does a table scan to resolve the version lock of each and every row) with 
SHOW STATUS (which computes table sizes based on the average of 1 random 
passes , like I described). Sorry to everyone I may have confused.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

SGreen@stripped wrote on 02/11/2005 10:04:02 AM:

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