Re: slow query, how can i imporve it?

[Shailendra Soni]

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