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 <[EMAIL PROTECTED]> 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, [EMAIL PROTECTED] > <[EMAIL PROTECTED]> wrote: > > > > See below.... > > > > Shailendra Soni <[EMAIL PROTECTED]> 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, [EMAIL PROTECTED] <[EMAIL PROTECTED] > > > com> wrote: > > > > > > > > > > > > Shailendra Soni <[EMAIL PROTECTED]> 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 > > > >
