View Full Version : Simple insert query taking 1-5 second(s)

04-20-2013, 12:02 PM
Can anyone lighten me up why this simple query is taking so much time?
The table has very few rows (like 500-700) and only 3 columns.

Table properties:
keyname: user
Column name: user

Query_time: 2.008483 Lock_time: 0.000085 Rows_sent: 0 Rows_examined: 0
use mydatabase;
SET timestamp=1366448462;
INSERT INTO `tablename` (user) VALUES('74763')

Isnt it irritating to see this query taking 1-5 seconds?
Any advice?

04-20-2013, 03:43 PM
Try this

update tablename set timestamp=1366448462 where user=74763;

04-20-2013, 05:06 PM
I think I confused you with the word Query.

Actually i got this report from my mysql slow_query_log:
Time to execute: 2.008483 seconds
Lock_time: 0.000085
Rows_sent: 0
Rows_examined: 0
Database used: mydatabase;

Query was: "INSERT INTO `tablename` (user) VALUES('74763')"

Additionally, can you tell which query should run faster?
"update `table` set `point` = '100'"
"update `table` set `point` = `point` + '1'"

04-20-2013, 06:38 PM
@sanomani, Old Pedant is back and will give you a far better answer than i did or will.

Old Pedant
04-21-2013, 03:37 AM
Back from the front?

Actually, I don't understand it, either.

I will say that you should not put apostrophes around numeric values (e.g, '74763') unless the field you are inserting into (in this case, user) is *NOT* a numeric data type.

But that shouldn't cause more than a millisecond or two of extra time for a single insert.

I am wondering if perhaps you are doing this on an overloaded server.

Instead of looking at the query time, look at the lock time: 0.000085, or 85 MICROseconds. In other words, that's all the time that MySQL used to lock the needed DB elements while performing the INSERT. So where did the other 2 seconds go? Possibly into simply waiting until the table was unlocked by some other user or process?

Are other kinds of queries on this database similarly slow? Or is it just the inserts?

Old Pedant
04-21-2013, 03:40 AM
Additionally, can you tell which query should run faster?
"update `table` set `point` = '100'"
"update `table` set `point` = `point` + '1'"

Once again, I have to ask: Why didn't you simply TRY IT???

I would expect them to both run at nearly identical speed.

You are changing the value of the field point in EACH AND EVERY RECORD of the table. So MySQL has no choice but to simply scan the entire table and change every record. I doubt seriously that the slight extra overhead of fetching the field value before changing it will ever be noticeable.