Re: slow query, how can i imporve it?
Chris, - Original Message - From: "Chris Elsworth" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, February 12, 2005 2:14 PM Subject: Re: slow query, how can i imporve it? On Fri, Feb 11, 2005 at 10:45:46AM -0500, [EMAIL PROTECTED] wrote: 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 Hello all, You just reminded me about this, I've been meaning to ask; are there any plans to "fix" this for InnoDB? It seems like quite a serious omission that InnoDB doesn't keep an accurate internal row count. Are there technical reasons why this isn't done, or is it in the TODO for any time soon? It's really one of the biggest things stopping me from switching wholly to InnoDB :( it is in the TODO: http://www.innodb.com/todo.php Note that most transactional databases do not keep a row count. There are performance reasons and technical problems in it. -- Chris Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow query, how can i imporve it?
Chris Elsworth wrote: On Fri, Feb 11, 2005 at 10:45:46AM -0500, [EMAIL PROTECTED] wrote: 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 Hello all, You just reminded me about this, I've been meaning to ask; are there any plans to "fix" this for InnoDB? It seems like quite a serious omission that InnoDB doesn't keep an accurate internal row count. Are there technical reasons why this isn't done, or is it in the TODO for any time soon? It's really one of the biggest things stopping me from switching wholly to InnoDB :( Shawn answered this in his previous message. "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." In other words, yes, there is a technical reason why an accurate internal row count cannot be kept for InnoDB tables, because the answer to COUNT(*) is effectively connection specific. If I have added 12 rows but not yet committed, and Shawn has added 23 rows but not yet committed, then you, Shawn, and I each get different answers for COUNT(*). Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow query, how can i imporve it?
On Fri, Feb 11, 2005 at 10:45:46AM -0500, [EMAIL PROTECTED] wrote: > 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 Hello all, You just reminded me about this, I've been meaning to ask; are there any plans to "fix" this for InnoDB? It seems like quite a serious omission that InnoDB doesn't keep an accurate internal row count. Are there technical reasons why this isn't done, or is it in the TODO for any time soon? It's really one of the biggest things stopping me from switching wholly to InnoDB :( -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow query, how can i imporve it?
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 x\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: >
Re: slow query, how can i imporve it?
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 x\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 400 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 que
Re: slow query, how can i imporve it?
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 x\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 400 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 > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow query, how can i imporve it?
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 x\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 400 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
Re: slow query, how can i imporve it?
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 400 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
RE: slow query, how can i imporve it?
[snip] 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 400 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? [/snip] Faster hardware and/or more memory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
