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 [EMAIL PROTECTED] 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 <[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 > > > > > >
