0

In my mysql db I have the fields id, name, email, age, city, phone. In that order

Does the order in which I select/insert/update these matter, in terms of speed/other?

If I say

SELECT phone, city, name, id, age, email FROM tablename
// mixed up order

As opposed to

SELECT id, name, email, age, city, phone FROM tablename
// original order of columns

EDIT: not only for one small simple query (just an example). Concern is also for 100.000 rows and multiple/advanced queries and loops

Dharman
  • 30,962
  • 25
  • 85
  • 135
mowgli
  • 2,796
  • 3
  • 31
  • 68

1 Answers1

2

No, it doesn't matter what order you select from. To increase speed to you can use indexes or only select the columns you actually need. When selecting, they would be returned in the order you select. You can also use GROUP BY to group the result by a particular column.

"That won't make any difference.

However, omitting columns from the SELECT clause will make it run faster since it'll send less data over the network."

Source: Does the order of columns in a select statement affect query speed?

Community
  • 1
  • 1
iswinky
  • 1,951
  • 3
  • 16
  • 47
  • Yes ok. My concern was only, if the database uses extra time to find the correct columns if they are in mixed up order – mowgli Aug 01 '14 at 07:21
  • I'd say no. If it does, it's very negligible especially if it's a simple query. – iswinky Aug 01 '14 at 07:24
  • See bottom edit of answer.. Do you know this or are you guessing using instinct? ;) – mowgli Aug 01 '14 at 07:29
  • Guessing using instinct! haha. You could just test it yourself if it's that much of a concern? – iswinky Aug 01 '14 at 07:30
  • ;) Yeah I could. But some people know the answer, I'm sure. It's not a big concern, just wanna use best practice/methods – mowgli Aug 01 '14 at 07:32
  • Although the order in a select might not matter, the question also asked about insert and update. In the case of an update, the order matters if one column receives the value of another, and the value of the latter changes in the same update. For example, `UPDATE table SET date2 = date1, date1 = CURRENT_TIMESTAMP WHERE id = 1` makes the value of `date2`receive the previous value of `date1` (before the update), and `date1` receives the current date. If the order changes, both columns will end up with the same value. See: https://dev.mysql.com/doc/refman/8.0/en/ansi-diff-update.html – Lucas Basquerotto Jun 28 '21 at 15:49
  • As a side note, the OP was probably concerned more about speed, in which case the order in an update might not matter (if you don't use the value of one column in another). – Lucas Basquerotto Jun 28 '21 at 15:50