Mysql – SQL INSERT Query takes 2 seconds and sometimes much longer to execute

MySQL

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:

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!

EXAMPLE FOR THE TABLE DATA:
EXAMPLE

Best Answer

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.

QUERY #1

Each time you do an INSERT, you are doing this under the hood

SET @sql = 'insert t select null';
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

Within the stored procedure, you fully parse, compile, execute and deallocate structures for the prepared SQL statement 2 million times.

QUERY #2

Running insert t select null from(, you fully parse, compile, execute and deallocate structures for the prepared SQL statement ONCE !!!

The execution of the Cartesian joins is really a self-contained atomic operation. Though it does perform iterations ( O(n²) running time ) to form the result table, it is not tethered by creating/destroying query parsing structures with each iteration as you are doing in the stored procedure (despite an O(n) running time). The execution plan of the single INSERT may be uglier to behold, but will surely run faster.

The inline queries are most likely memory tables. The resulting join could be a temporary MEMORY table or temporary MyISAM table. Either way, the formation of the result set is a single operation.

CONCLUSION

Inserting 2 million rows on a single INSERT is always faster that than doing an INSERT of a single row 2 million times. In principle, this would be more evident if you connected, inserted and disconnected. Connecting once beats connecting 2 million times (See my post How costly is opening and closing of a DB connection?)

Mysql – Optimize WordPress Mysql Query that take 5 seconds to execute

Here is the modified query

select 
p.ID from wp_posts p
inner join wp_term_relationships wtr on p.ID = wtr.object_id
left join wp_postmeta wpm on p.ID = wpm.post_id and wpm.meta_key = '_Original Post ID'
where 1=1
and p.ID != 731467
and wtr.term_taxonomy_id = 5
and p.post_type = 'post'
and ( p.post_status = 'publish' or p.post_status = 'private')
and ( wpm.post_id IS NULL or ( wpm.meta_key = '_Original Post ID' and cast(wpm.meta_value AS CHAR) = 'deleted') )
group by p.ID
order by p.ID desc
limit 0,20

In the original query you have a left join to wp_postmeta again and this is not needed since you are using the filtering at the end which could refere to the joined table which is already done.

And also need to apply the index on the joining key on wp_term_relationships

alter table wp_term_relationships add index object_id_idx(object_id);

Now Or clause are usually killer and its better to convert them to union which are better than or

(
 select 
 p.ID from wp_posts p
 inner join wp_term_relationships wtr on p.ID = wtr.object_id
 left join wp_postmeta wpm on p.ID = wpm.post_id and wpm.meta_key = '_Original Post ID'
 where 1=1
 and p.ID != 731467
 and wtr.term_taxonomy_id = 5
 and p.post_type = 'post'
 and p.post_status = 'publish' 
 and wpm.post_id IS NULL 
)
union
(
 select 
 p.ID from wp_posts p
 inner join wp_term_relationships wtr on p.ID = wtr.object_id
 left join wp_postmeta wpm on p.ID = wpm.post_id and wpm.meta_key = '_Original Post ID'
 where 1=1
 and p.ID != 731467
 and wtr.term_taxonomy_id = 5
 and p.post_type = 'post'
 and p.post_status = 'private'
 and wpm.meta_key = '_Original Post ID' 
 and cast(wpm.meta_value AS CHAR) = 'deleted'
)
group by p.ID
order by p.ID desc
limit 0,20

Best Answer

Related Solutions

Mysql – Why is “select join insert” so much faster than “loop insert”

QUERY #1

QUERY #2

CONCLUSION

Mysql – Optimize WordPress Mysql Query that take 5 seconds to execute

Related Question