Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working on a table which holds around 300k records. I'm using a 3 columns unique index to prevent duplicate records. For some reason the database is taking long to insert new rows.

I have a few questions regarding UNIQUE indexes and efficiency:

  1. Will a UNIQUE index make a table much slower?

  2. Would it be useful to store old rows in another table and reduce the table to around 1k records?

  3. And most importantly is there any way to know how long my db takes to insert a row? In phpMyAdmin I can see the "time to process query" but only on SELECT queries
share|improve this question

migrated from stackoverflow.com Jan 12 '13 at 10:04

This question came from our site for professional and enthusiast programmers.

    
What do you consider "long" for inserting rows? –  Gordon Linoff Jan 12 '13 at 3:27
1  
For the point 3 you can try the (MySQLWorkbench)[dev.mysql.com/downloads/workbench/]. –  cheesemacfly Jan 12 '13 at 3:29
    
500ms for inserting a row. I haven't been able to test it but I believe is taking around that. –  Liso22 Jan 12 '13 at 3:34
1  
FWIW: 300K rows is not a big database. –  JohnFx Jan 12 '13 at 5:53
1  
I would suggest you to post your insert query and the definition of your table here, so that we are able to see why it takes too long to insert 300k records (it is a small size even for 3 indexes) –  Michael Jan 12 '13 at 10:31
show 2 more comments

3 Answers 3

up vote 0 down vote accepted
  1. Indexes do introduce some overhead for a database since the data must be inserted and then the index managed. Does it make the table much slower? That depends a lot on how many rows, how many indexes, how you organize the tablespaces, your filesystem, etc. In general, a couple of indexes on a table should not be noticeable on a properly operating database.

  2. Yes. Many database engines provide for Range Partitioning based on ideas like this, some qualification that separates older, not-changing-much data to be managed separately. This can be done by moving data to another table, or allowing the engine to manage the data with Range Partitioning. see this for MySQL

  3. You should use another client to measure this - something that can give you those statistics. MySQLWorkbench as been suggested. Other tools might include DBVisualizer, SQuirrel, etc.

share|improve this answer
    
Partitioning by it self may slower the queries, only in case where you specify the partition field in a query in a correct manner , it may boost the performance. partition by it self is not intend to improve performance. –  Michael Jan 12 '13 at 10:29
add comment

Indexes will take some time to make them self upto date and if you are using myisam then use DELAY_KEY_WRITE=1 which make all indexes to be updated after table closed and try to keep the datatype size as less ad possible to fit your requirement and reduce the key length if possible

I think it is not needed to create other table just partition the table

In phpMyAdmin it is possible only when you write insert query in sql dialogue box

or you just go to command line and log into mysql and set profiling which will give brief description why your query is slow.........

share|improve this answer
add comment

For write operations (e.g. INSERT and DELETE) having a composite index will slow things down. However, MySQL can handle tables with MANY more rows than that. If you need to keep the row count to 1k to maintain performance then I would suggest something is very wrong somewhere. Regardless, I would strongly suggest not breaking the data up in this way, you're just giving yourself a maintenance headache further down the line...

What datatypes are the columns that make up the composite index?

share|improve this answer
    
4 varchar columns: 5, 5, 5 and 40 characters long each. It's very poorly set up, the last varchar columns actually holds a date but I cannot do anything about it except maybe change the index. –  Liso22 Jan 12 '13 at 4:48
    
would it help if I created a KEY column with the four cols concatenated as value and used it as UNIQUE index? I think inserting is taking +500ms –  Liso22 Jan 12 '13 at 4:49
    
Ouch, yeah, that would slow things a bit. Regarding the concatenation, that would help, but I'm not sure to what extent... You could even add them all together, then md5 the result to bring the column size down to 32. Be aware that md5 hashes are not guaranteed to be unique though. However, according to truerwords.net/1740 the odds of a clash are 1 in 340,282,366,920,938,463,463,374,607,431,768,211,000... –  MatW Jan 12 '13 at 5:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.