Optimal table order for mySQL "join style" query

[ChenXiu]

For a mySQL query on 4 tables that will be joined on the "sku_number varchar(20) PRIMARY KEY" column, does the ordering of the tables in the query affect performance (speed) ?

• I need a large-data longtext column from Table "a"
• I need a small varchar(9) column from Table "b"
• I need 5 medium sized (varchar(255)) columns from Table "c"
• I need 2 small varchar(55) columns from Table "d"

Will the speed be just as fast if I do the query in alphabetical order (e.g. "select a.bigdata, b.color, c.weight, c.height, c.depth...")
-or-
Would the query execute faster if done from the "smallest data size - to - biggest data size" (e.g. "select b.color, a.bigdata...."
... and, should "how many columns I need from a table" affect the order?

Thank you in advance.

[requinix]

4 hours ago, ChenXiu said:

For a mySQL query on 4 tables that will be joined on the "sku_number varchar(20) PRIMARY KEY" column, does the ordering of the tables in the query affect performance (speed) ?

Basically no. The query optimizer will rearrange the query as it sees fit according to heuristics that are probably better informed than you are. Generally, you want to write the query in the most logical way and let MySQL execute it however it wants.

Post your query and an EXPLAIN for it.

[gizmola]

The fields that are part of the result from 4 joined tables, isn't really important in comparison to the number of rows in the result set, and any limiting where clause criteria.  

As @requinix stated: EXPLAIN query...  is your analysis tool.  We'd need to see the query and its explain plan to offer further insight.  

Many times, if performance is bad, you'll be able to see the reasons in the explain.  Depending on the criteria, adding a covering index might solve your performance issue, but the only way to know for sure is via the explain.  You want to take a look at the rows and key columns to see how many rows are being examined, and what indexes (if any) are used in generating the final result.