Which one is best ?

[Marco]

Currently I have 3 mysql tables with about 6 columns each, sometimes I have
to access to all of them at the same time but most of the times I access 2
at the same time.

I was wondering which is fast making a single table with about 18 columns,
and each time select the columns that I want in the select query, this way I
would do less select queries.
Or is it better like I have 3 tables, and access 2 of them at the same time
most of the times?

Probably I should post this in a MySQL news server, anyway if someone has
some thoughts about it don't be shy ;)

Thx
Marco

[Aggro]

Re: Which one is best ?

Marco wrote:
[color=blue]
> I was wondering which is fast making a single table with about 18 columns,
> and each time select the columns that I want in the select query, this way I
> would do less select queries.
> Or is it better like I have 3 tables, and access 2 of them at the same time
> most of the times?[/color]

There is no simple yes or no answer to it, it depends much about what
kind of queries you need to make and how many rows you are supposed to
have in each table.

For example:
If you have 100 000 rows in all tables, and all rows are linked 1 and
only 1 row in other tables, you propably would be better putting all in
one table.

Then again, if you have table with 100 000 rows and another table with
about 10 rows, and each row has a string that you want to do a query
like: select * from nametable where name like '%middletext%';

It would be faster to use different tables. Because if you would have 1
table, you would have to search all rows in it, because you couldn't
benefit from indexes. But when using two tables, you would find the
names by searching 10 rows and then you could use name_id in your larger
talbe, which would be of course indexed.

These are only two examples, but I hope you understant what I'm trying
to say: There is no simple answer to this question.

[Dasher]

Re: Which one is best ?

MySQL is a relational database it is designed to be fast with multiple table
accesses. I have queries that access 5 tables at once. It is still fast.

If you can do the query in a simple wide table... you really don't need a
relational database.



"Marco" <mpgtlatbluewin dotch> wrote in message
news:4009341e$1 _1@news.bluewin .ch...[color=blue]
> Currently I have 3 mysql tables with about 6 columns each, sometimes I[/color]
have[color=blue]
> to access to all of them at the same time but most of the times I access 2
> at the same time.
>
> I was wondering which is fast making a single table with about 18 columns,
> and each time select the columns that I want in the select query, this way[/color]
I[color=blue]
> would do less select queries.
> Or is it better like I have 3 tables, and access 2 of them at the same[/color]
time[color=blue]
> most of the times?
>
> Probably I should post this in a MySQL news server, anyway if someone has
> some thoughts about it don't be shy ;)
>
> Thx
> Marco
>
>[/color]

[Dan Tripp]

Re: Which one is best ?

Marco wrote:[color=blue]
> Currently I have 3 mysql tables with about 6 columns each, sometimes I have
> to access to all of them at the same time but most of the times I access 2
> at the same time.
>
> I was wondering which is fast making a single table with about 18 columns,
> and each time select the columns that I want in the select query, this way I
> would do less select queries.
> Or is it better like I have 3 tables, and access 2 of them at the same time
> most of the times?
>
> Probably I should post this in a MySQL news server, anyway if someone has
> some thoughts about it don't be shy ;)
>
> Thx
> Marco
>
>[/color]

I've always leaned toward implementing a more highly normalized (more
granularized) form. The DB developers I've worked with all lean towards
this approach as well.

In the past I've seen how easily I could have "shot myself in the foot"
if I hadn't normalized. Here are some links to articles I thought were
pretty decent:

http://www.serverwatch.com/tutorials/article.php/1549781

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=4887&pg=1

http://www.devshed.com/c/a/MySQL/An-Introduction-to-Database-Normalization/

Regards,

- Dan

http://www.dantripp.com/