Continuing the discussion from Nested while loop problem very slow in query:
re using explicit fieldnames vs. the asterisk wildcard, it is my understanding that with MySQL at least, using the wildcard makes MySQL need to identify the fieldnames. A negligible performance hit perhaps, but just the same.
@r937 ?
In order to be 100% we would need to look at the logic in mysql’s source code. When the asterisk is used then mysql has to refer to its internal metadata definitions. But if separate columns are provided it also has to query its internal metadata at least to check if the provided columns are valid - so in theory separate column names should be even slower.
However, these differences are so minuscule that I can’t imagine it making any difference in any kind of usage. I just noticed that many people repeat the idea that listing all columns is faster than using the asterisk while I found this not to be true.
I don’t follow this logic. I agree with you that we don’t know how MySQL (or any other database) works internally but this doesn’t make sense to me.
I would think that using the asterisk will be slower and I don’t ever recall to have read that it is faster.
What you say could be true if the code to check the name of each field used in the query is a function not built in the query executing code routine.
If it is not, built in the routine, maybe you can avoid checking if the name of a field exists since it is being retrieved and therefore it has to be right. If it is, using the asterisk will involve one more step: retrieving the list of fields in that table.
If you’re selecting ALL the fields in a table, then yes, SELECT * can out perform. But if you’re selecting a limited dataset, there is an uneeded performance hit, especially if that table is returning one of the obnoxious data types (BLOB, TEXT, etc).
In the sample SELECT which this thread was pulled from, there was a SELECT * from four different tables when there were a limited number of fields that were needed. Why have that extra bandwidth if it’s not needed?
Me, neither. I’ve never read that the asterisk is faster but I’ve read many times that it is slower (which I found not be true).
I don’t know what you mean by ‘a function not built in the query executing code routine’. Mysql query parser has to verify if each column name in the query is a valid one and exists. Whether this is some kind of function, method or some other mechanism - I have no idea without knowing the source code.
The list of fields needs to be retrieved whether the asterisk is used or not. Without the asterisk the list is retrieved from the query while with the asterisk the list needs to be retrieved from the table definition. Which is faster - I don’t know. What I’m sure mysql has to do with a list of fields in a query - it has to first parse the list (parsing a long list of fields theoretically should take longer than parsing a single asterisk) and then it has to compare each field with its table definition if it exists there - otherwise an error is thrown. We can speculate which takes longer - you can benchmark this yourself. My tests have shown that there is no speed difference - meaning that any differences are so small that they are impossible to measure. Query parsing - with asterisks or not - is so fast that its overhead disappears in comparison to what is the real overhead: query execution and fetching data.
Yes, in that case this is good approach. However, there are cases where all fields are always fetched - for example ORM frameworks fetch all row data into a single object because that’s what they are supposed to do. In such a case I think it’s perfectly fine to use an asterisk since the query is shorter and clearer, requires less maintenance and there’s no speed difference.
[quote=“Mittineague, post:2, topic:98256, full:true”]@r937 ?[/quote]mysql has to look up the field names in both cases
SELECT column1, column2, wtf_is_this, column4 FROM …
how else is mysql gonna know that wtf_is_this is an actual column in the table?
Thanks for correcting my faulty memory.
So it is a “getting unsed data” vs. “only getting what you use”.
Now to remember that - and hopefully not forget 10 other things 