Is there a speed difference in MySQL operations?

Question

I was discussing speed of MySQL statements and someone asked me: Between SELECT, INSERT, UPDATE, and DELETE, which is faster and why?

I didn't know the answer. Does anyone know if one is faster than the other, and if so, why? Or are they all generally the same speed?

Answer 1

I'm not sure about each command individually, but a lot of it depends on the actual statement. Having indexes on a table require that indexes be modified on inserts, updates and deletes. While an index may make selects faster, it slows down any data modification statements. In addition to that, the complexity of the query, the datatype of the columns, any sql functions (like aggregates) all change the performance of a query.

Answer 2

Mostly "it depends".

For example, if the database is serving a lot of concurrent users then the amount of time the table is locked is going to matter more than the amount of time the query takes in total.

As a rule of thumb, I would say:

INSERT is usually fastest, as it only adds data and doesn't need to lock the table because no existing data is modified. Lots of indexes could slow it down a little though.

SELECT can potentially be very fast or very slow. It depends on the query. It has the potential to be the fastest, and it doesn't lock the table. But SELECT operations are often a lot more complex than the others, fetching data from multiple tables, or sorting or using indexes more comprehensively.

DELETE is relatively slow. The table (or depending on the storage engine, the affected rows) needs to be locked. Indexes need updating.

UPDATE is usually the slowest. The table (or depending on the storage engine, the affected rows) need to be locked, indexes need updating, and it is slightly more involved than DELETE.

But as I said, this is all a rule of thumb, and it really depends a lot on circumstances.

Answer 3

You're not quite asking the right question. SELECT, for example, has to compute, in some fashion, the set of all rows that match the selection, and then return them via some mechanism that may be anything from a big-ass dynamic array in something like sqlite (because it's in memory and in the same process) to data passed remotely across the internet.

So, for example, SELECT * FROM USERDATA returning 10 rows locally might take microseconds, but take minutes if it's returning 10,000,000 rows across a WAN.

Answer 4

Assuming simple queries and common usage scenarios and configurations, SELECTs will be fastest because they're both most easily cached (by everything - the CPU, disk, OS, MySQL memory, and the MySQL query cache) and most frequently executed (allowing said caches to be useful).

All other operations are writes, which are much more time consuming and cannot be cached. The operating time of even simple write queries is often highly dependent on how much read load (SELECT queries) the server is under.

Answer 5

Speed in database queries can only really be measured as a relative to each other. For most simple queries, it's a nominal difference in terms of milliseconds.

The better problem should be viewed from the point of view of 'how many things are you effecting?'.

• A query can take on a variety of forms but if you are selecting from 3 tables, it will likely be slower than 2 tables.
• Using LIMIT will help the query to execute quicker in all cases
• Indices can help a query if it uses them properly
• Updating indices takes time
• Larger tables take more time to work through

The list goes on, but usually I'd built and work on the slow spots after the fact. Most people don't really push their databases all that hard anyways:)