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 know the question's title is bad, But I have an hard time figure it out my self... sometimes this query runs in 2-3 seconds and sometimes it's takes even 30 seconds

I have a table named ranking_temp_free: ranking_temp_free

I have one Index except the primary key:

Index

I have no triggers or partitions on this table..

I'm using this Query:

INSERT IGNORE INTO ranking_temp_free 
(countryID, categoryID, ranking, popID, app_id, check_time) 
VALUES (/*Here comes 50,000 rows of Data*/);

And as I wrote before.. The time that takes the query to finish is not fixed as I wish to be...

Logs:

THERE ARE MORE LOGS BEFORE..

07-02-2013 00:24:37 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:24:54 Inserted: 50000 rows
QUERY TIME : 17 SECONDS

07-02-2013 00:25:05 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:25:41 Inserted: 50000 rows
QUERY TIME : 36 SECONDS

07-02-2013 00:26:02 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:26:07 Inserted: 50000 rows
QUERY TIME : 5 SECONDS

07-02-2013 00:26:28 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:26:35 Inserted: 50000 rows
QUERY TIME : 7 SECONDS

07-02-2013 00:26:46 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:26:52 Inserted: 50000 rows
QUERY TIME : 6 SECONDS

07-02-2013 00:27:03 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:27:17 Inserted: 50000 rows
QUERY TIME : 14 SECONDS

07-02-2013 00:28:19 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:28:39 Inserted: 50000 rows
QUERY TIME : 20 SECONDS

AND SO ON.........

It's a simple INSERT so why the time is different?.

Just to be clear: THERE ARE NO ANY OTHER PROCESSES ON THE SQL SERVER! PROCESSES

EXAMPLE FOR THE TABLE DATA: EXAMPLE

share|improve this question

migrated from stackoverflow.com Feb 7 '13 at 6:33

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

2  
Does the size of the data to be inserted vary widely? –  Mike Brant Feb 6 '13 at 22:51
    
@MikeBrant hmmm... no. I just added an example in my post –  Ofear Feb 6 '13 at 22:56
    
Do you have wide variances in the number of rows which may not be inserted due to the IGNORE definition? –  Mike Brant Feb 6 '13 at 23:01
2  
It would be really hard to say without further understanding what sort of settings you have on your MySQL server (how much memory is allocated, insert buffer sizing, etc.). At this point, I think this is a question that is much more appropriate to dbastackexchange. –  Mike Brant Feb 6 '13 at 23:12
1  
depending on what you're inserting, try increasing the index fill factor. inserting that many rows will cause page splits depending on your current settings (the index will be writte/split across more than 1 page of memory) msdn.microsoft.com/en-us/library/ms177459.aspx ... you could alternatively disable the index, perform the insert, then rebuild it. –  elvis Feb 6 '13 at 23:34

1 Answer 1

up vote 1 down vote accepted

We found the solution to the slow random query execution time:

I added this line: SET TRANSACTION ISOLATION LEVEL READ COMMITTED right after the DB connect.

we are using PHP, So it's looks like this:

mysql_select_db($db, $link);
mysql_query("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");

All our problem solved with this magic line.

Now our queries run in 40 minutes and not in 2.5 hours as it use too..

thanks all for your help.

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.