18 messages in com.mysql.lists.mysqlRe: SET vs Join (was: what's faster: ...| From | Sent On | Attachments |
|---|---|---|
| albert braun | 10 Jun 1999 17:17 | |
| Don Read | 10 Jun 1999 18:28 | |
| Benjamin Pflugmann | 10 Jun 1999 20:31 | |
| albert braun | 11 Jun 1999 12:51 | |
| Benjamin Pflugmann | 12 Jun 1999 12:13 | |
| albert braun | 14 Jun 1999 10:34 | |
| Paul DuBois | 14 Jun 1999 11:01 | |
| chas | 14 Jun 1999 12:14 | |
| Roger Smith | 14 Jun 1999 12:51 | |
| Roger Smith | 14 Jun 1999 12:53 | |
| Roger Smith | 14 Jun 1999 13:56 | |
| Paul DuBois | 14 Jun 1999 17:17 | |
| chas | 14 Jun 1999 18:11 | |
| Blake Binkley | 14 Jun 1999 20:49 | |
| Christian Mack | 16 Jun 1999 03:25 | |
| Benjamin Pflugmann | 20 Jun 1999 07:50 | |
| chas | 26 Jun 1999 20:14 | |
| Christian Mack | 15 Jul 1999 11:28 |
| Subject: | Re: SET vs Join (was: what's faster: a join or searching a column with the % wildcard?) |
|---|---|
| From: | Benjamin Pflugmann (phil...@spin.de) |
| Date: | 06/20/1999 07:50:10 AM |
| List: | com.mysql.lists.mysql |
Hi!
On Tue, Jun 15, 1999 at 04:15:01AM +0900, pan...@skinnyhippo.com wrote:
Although I have never speed-tested it myself, using a set for the degree (if you know for sure that you will use less than 64 degrees at all) should be fastest (looks like proposal a., but will be faster, because it uses integer arithmetic internally):
Sorry - I've been mulling the SETs vs Joins for a while and wondering if there is a cut-off at which the Join wins. Are you saying that that would be 64 ? ie. Joining a second table is better than a SET of 64+ members ?
No. I am saying that the SET type in MySQL actually only supports up to 64 members. IIRC, support for larger SETs is planned somewhen, but I don't recall the details.
(I realise that it may depend on other factors such as the number of rows and number of columns in the table containing the SET column)
Well, IMHO there is no rule of thumb for this. Even when MySQL supports large sets, it depends on for what purpose you use them.
For example, performance also depends on how much members you usually assign from the set. If your set contains a thausand possible members, but your objects only apply to two of those, using tables would be much faster. If your object apply to 90% of them, SETs should be faster.
Sets have their special drawbacks. You have to decide yourself if you want to handle them.
One was mentioned (need to use ALTER TABLE to add members after the table is created) but what else is there ?
The other one was the limit of members.
Without any claim for completeness I would say the (dis-)advantages of SETs against a second table are:
SET + less memory consumption + faster overall access - restriction on number of members - its more complex (and slower?) to modify the members
Personally, I use SETs whenever I consider the content of the set to be quite static, tables otherwise. Speed requirements may influence this decision.
Bye,
Benjamin.