Re: How Long Should It Take To Create an Index?
John, I would guess with about 100,000 rows, it might take a couple minutes to create an index on two varchar-255 columns. With modern hardware anyway. Very rough estimate. Factors include amount of RAM, speed of disks, speed of processors, other processes running and either locking table or just using resources. Do a SHOW PROCESSLIST; to see if your index creation is blocked. Dan On 11/17/06, John Kopanas [EMAIL PROTECTED] wrote: I have a mysql table with 100K rows approximately. We are creating the following index: create index closed_base_cust_and_job on backlog_dev.closed_bases(cust_no, jobno); Both cust_no and jobno are varchars(255) There is 56 fields in the table and no other indeces except on the primary key. Should it be taking a long time? How long would some estimate it takes? Is there a way to estimate how long it takes? What factors affect the time it takes to create an index? Thanks for your help. Your Friend, -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How Long Should It Take To Create an Index?
Scratch that, I just created a 10 row table with 2 varchar255's. Creating a 2 col index on it took 2.09 seconds. Could take longer due to all the other fields you have in your table but a couple of minutes at the outside. Dan On 11/17/06, Dan Buettner [EMAIL PROTECTED] wrote: John, I would guess with about 100,000 rows, it might take a couple minutes to create an index on two varchar-255 columns. With modern hardware anyway. Very rough estimate. Factors include amount of RAM, speed of disks, speed of processors, other processes running and either locking table or just using resources. Do a SHOW PROCESSLIST; to see if your index creation is blocked. Dan On 11/17/06, John Kopanas [EMAIL PROTECTED] wrote: I have a mysql table with 100K rows approximately. We are creating the following index: create index closed_base_cust_and_job on backlog_dev.closed_bases(cust_no, jobno); Both cust_no and jobno are varchars(255) There is 56 fields in the table and no other indeces except on the primary key. Should it be taking a long time? How long would some estimate it takes? Is there a way to estimate how long it takes? What factors affect the time it takes to create an index? Thanks for your help. Your Friend, -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How Long Should It Take To Create an Index?
That is what I thought. Then why would it be going at 99% CPU for over 20 minutes? What factors would cause that? We are using version 4.1.20standard. Would the fact that a website was still connected to DB cause the problem? Dan you rock! On 11/17/06, Dan Buettner [EMAIL PROTECTED] wrote: Scratch that, I just created a 10 row table with 2 varchar255's. Creating a 2 col index on it took 2.09 seconds. Could take longer due to all the other fields you have in your table but a couple of minutes at the outside. Dan On 11/17/06, Dan Buettner [EMAIL PROTECTED] wrote: John, I would guess with about 100,000 rows, it might take a couple minutes to create an index on two varchar-255 columns. With modern hardware anyway. Very rough estimate. Factors include amount of RAM, speed of disks, speed of processors, other processes running and either locking table or just using resources. Do a SHOW PROCESSLIST; to see if your index creation is blocked. Dan On 11/17/06, John Kopanas [EMAIL PROTECTED] wrote: I have a mysql table with 100K rows approximately. We are creating the following index: create index closed_base_cust_and_job on backlog_dev.closed_bases(cust_no, jobno); Both cust_no and jobno are varchars(255) There is 56 fields in the table and no other indeces except on the primary key. Should it be taking a long time? How long would some estimate it takes? Is there a way to estimate how long it takes? What factors affect the time it takes to create an index? Thanks for your help. Your Friend, -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info
Re: Re: Re: How Long Should It Take To Create an Index?
On 11/17/06, John Kopanas [EMAIL PROTECTED] wrote: That is what I thought. Then why would it be going at 99% CPU for over 20 minutes? What factors would cause that? We are using version 4.1.20 standard. Would the fact that a website was still connected to DB cause the problem? Normally, no - an index creation is an atomic operation. It waits until it has a table lock, then does its thing, and then it is done. It waits for connections, then while it is working other connections wait for it. I would guess that perhaps you have somehow encountered a deadlock. Can you post the output of SHOW PROCESSLIST ? -Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
