CODEWITHSUNDEEP
mysqljoinperformance
JMW
JMW

Reputation: 7807

performance: mysql and joins

it's just a question about modeling the tables.

i've a user table like:

then there are some tables, that reference to the user like the group and log tables...

i'm now thinking about adding an id as autoincerment unsigned integer to the user table. because someone told me, that joins over integers are faster, than joins over varchars.

To me this sounds reasonable because there are less bytes, that have to be compared.

Is it true? Or would you say, that there is no need to introduce an id column to the user table?

thanks a lot in advance :)

Upvotes: 0

Views: 169

Answers (2)

Ronnis
Ronnis

Reputation: 12833

Technically, integers should be just a tad faster for the reasons you wrote. However, the real difference in your case is that you choose between having a natural key or a syntetic key.

If you have a stable (meaning it does NEVER EVER chance) natural key, you gain one significant performance advantage. You can often completely remove the join to user table since you can query directly on the other tables, like:

select ...
  from user_posts 
 where userid = 'JMW'

Regarding the INT/VARCHAR disussion, here is an article that may interest you.

http://www.mysqlperformanceblog.com/2008/01/24/enum-fields-vs-varchar-vs-int-joined-table-what-is-faster/

Upvotes: 2

Pierre
Pierre

Reputation: 146

MySQL, depending on the engine you use, can be horribly slow when using varchar indexes, especially InnoDB. MyISAM will be pretty fast but it's not ACID compliant so you should abandon it.

This is commonly true with all DBMS, integers are always faster than strings (you compare it using artithmetical über fast CPU operations instead of comparing strings char to char).

When you proceed with JOIN select in SQL, always do it on indexed fields, weither there are integers or not, this probably is the most important advice for you here.

EDIT: In practice, most DBMS will optimize varchar indexes for reading, InnoDB uses BTREEs and hashed strings which makes it quite fast in read operations. InnoDB still suffers from varchar indexes slow write, I can't find the associated bugs, but they still exists as open MySQL bugs in the MySQL official bug tracker. Always void varchar indexes when planning to use InnoDB.

Upvotes: 2

Related Questions