speed

On Sun, 20 Feb 2005 14:36:25 +0000, Armando Padilla wrote:

 > windandwaves wrote:
  >> Hi Folks
  >>
  >> I am putting together a couple of tables in PHP/MySql that are rather large:
  >>
  >> 137 fields with at least about seventy medium text field.
  >>
  >> The table only contains about 500 rows and will never grow substantially (well, it would be nice (they are members).
  >>
  >> I could have created a dozen or so tables, which would have been a lot sexier from a database design perspective, but as I am a PHP
  >> newby, I did not like that idea.
  >>
  >> Should I be worried about speed? and if so, how I can I measure speed.
  >>
  >> I know it all depends.
  >>
  >> I use a standard ISP package (not a dedicated server). I expect only a handful users of the website at the same time. These large
  >> tables are read by a page that displays all the information (address, characteristics, prices, etc...) from all 136 fields. No
  >> difficult queries are used at all.
  >>
  >> I would love to hear any experiences that you may have had that can help me.
  >>
  >> - Nicolaas
  >>
  >>
 > Hey there,
 >
 > here is my 2 cents on this issue. I think a PHP programer must at least
 > be somewhat well versed in database design. (personal opining) Being
 > that much of what we do is manipulate adn manage data that a web user
 > sends and receievs from our websites. From a scalable sense you really
 > need to Normalize your tables. This will allow you to scale your
 > application up down the road. Also try to add Indexes to your table
 > structure, use the EXPLAIN function for mysql to check out how well your
 > connections are doing, and if you will have a large amount of people
 > coming to your site go ahead and open persistant connections to mysql
 > (mysql_pconnect)
 >
 > If you want to test how fast your app is going to run (or running) try
 > benchmarking it,
 >
 > /***
 > * START OF PAGE
 > *****/
 > $starttime = time();
 >
 > /****
 > * END OF PAGE
 > ******/
 > $endtime = time();
 >
 > echo "Page took: ".$endtime-$starttime." to load.";
 >
 >
 > I dont know if there are any apps out there that will determine how fast
 > a page will load when X amount of users hit the site but you might want
 > to google that.
 >
 > Well hope this helps.
 > Armando Padilla

Armando,

Have you come across any online material regarding database design you
would recommend?

Cheers,

Chris
 >> Stay informed about: speed 

I noticed that Message-ID:
from Chris Matchett contained the following:

 >Have you come across any online material regarding database design you
 >would recommend?

<a rel="nofollow" style='text-decoration: none;' href="http://www.databasejournal.com/sqletc/article.php/1428511" target="_blank">http://www.databasejournal.com/sqletc/article.php/1428511</a>
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs <a rel="nofollow" style='text-decoration: none;' href="http://www.ckdog.co.uk/rfdmaker/" target="_blank">http://www.ckdog.co.uk/rfdmaker/</a>
 >> Stay informed about: speed 

"Armando Padilla" wrote in message
[....]
 > here is my 2 cents on this issue. I think a PHP programer must at least be somewhat well versed in database design. (personal
 > opining) Being

I am, it is the PHP that I lack

 > that much of what we do is manipulate adn manage data that a web user sends and receievs from our websites. From a scalable sense
 > you really need to Normalize your tables.

It is very unlikely that they will grow datawise to more than about 5,000. It is in fact physically impossible.

 > This will allow you to scale your application up down the road. Also try to add Indexes to your table

I have

 > structure, use the EXPLAIN function for mysql to check out how well your connections are doing, and if you will have a large
 > amount of people

how does this work?

 > coming to your site go ahead and open persistant connections to mysql (mysql_pconnect)
 >
 > If you want to test how fast your app is going to run (or running) try benchmarking it,

I thought about that, brilliant.


 >
 > I dont know if there are any apps out there that will determine how fast a page will load when X amount of users hit the site but
 > you might want to google that.
 >
 > Well hope this helps.
 > Armando Padilla


Thank you Armando
 >> Stay informed about: speed 

"Henk Verhoeven" wrote in message
 > Hi Nicolaas
 >
 > It is easy to put all data in one table, but it is even easier to put a lot of errorneous data in one table. Soon you (or your end
 > users) will experience 'database pollution'. When the table holds repeated data (if you do no databas desing it usually does) the
 > pollution gets really irritating as things that somehow belong together can in practice often not be found 'together' in the
 > database.
 >
 > To make a long story short, a well designed database is a good foundation for proper application that helps the users to maintain
 > the data and prevent database pollution. So i suggest speed should not be your only design criterium. But IF your only concern is
 > speed: why don't you just try it out?
 >
 > Greetings,
 >
 > Henk Verhoeven,
<font color=purple> > <a rel="nofollow" style='text-decoration: none;' href="http://www.phpPeanuts.org</font" target="_blank">www.phpPeanuts.org</font</a>>
 >
 > P.S. The aim of well designed code is very similar to a well designed database: make maintenance easier (i mean maintenance of the
 > code). So are the means: avoid repetition. So learning about database design may in time bring you more then 'just' statisfied end
 > users
 >
 > windandwaves wrote:
 >
  >> Hi Folks
  >>
  >> I am putting together a couple of tables in PHP/MySql that are rather large:
  >>
  >> 137 fields with at least about seventy medium text field.
  >>
  >> The table only contains about 500 rows and will never grow substantially (well, it would be nice (they are members).
  >>
  >> I could have created a dozen or so tables, which would have been a lot sexier from a database design perspective, but as I am a
  >> PHP newby, I did not like that idea.
  >>
  >> Should I be worried about speed? and if so, how I can I measure speed.
  >>
  >> I know it all depends.
  >>
  >> I use a standard ISP package (not a dedicated server). I expect only a handful users of the website at the same time. These
  >> large tables are read by a page that displays all the information (address, characteristics, prices, etc...) from all 136 fields.
  >> No difficult queries are used at all.
  >>
  >> I would love to hear any experiences that you may have had that can help me.
  >>
  >> - Nicolaas

Maintainabilility is my main concern. In that way I designed the database. However, I was worried that I may perhaps give away
too much speed.

I totally understand your point above. I usually make supersleek databases with not a dot of repeated info. However, you can go too
far in normalisation.

In this particular instance. We are talking about 500 B&Bs, each with their own pieces of information. For example, each B&B has a
list of about 15 facilities. About 10-20% of them are repeated, so I could put it in a table like this:

Main table
unique facilities list
table with link between main table and facilities

That would be the proper way to do it. But all the extra SQL, in my opinion, in this instant, with only 10-20% repeat, does not
weigh up against the joy of having the fields F1 to F25 in the main table.

Search wise it is obviously a little harder to search 25 fields, but I simply make one field with all the words in it everytime the
user updates their data (this fields is called Keywords) and then search that. I know it is really ugly database design, but it
allows me to achieve my goal (finish the website!).

Cheers

- Nicolaas
 >> Stay informed about: speed 

Hi Nicolaas

It is easy to put all data in one table, but it is even easier to put a
lot of errorneous data in one table. Soon you (or your end users) will
experience 'database pollution'. When the table holds repeated data (if
you do no databas desing it usually does) the pollution gets really
irritating as things that somehow belong together can in practice often
not be found 'together' in the database.

To make a long story short, a well designed database is a good
foundation for proper application that helps the users to maintain the
data and prevent database pollution. So i suggest speed should not be
your only design criterium. But IF your only concern is speed: why don't
you just try it out?

Greetings,

Henk Verhoeven,
<a rel="nofollow" style='text-decoration: none;' href="http://www.phpPeanuts.org" target="_blank">www.phpPeanuts.org</a>

P.S. The aim of well designed code is very similar to a well designed
database: make maintenance easier (i mean maintenance of the code). So
are the means: avoid repetition. So learning about database design may
in time bring you more then 'just' statisfied end users

windandwaves wrote:

 > Hi Folks
 >
 > I am putting together a couple of tables in PHP/MySql that are rather large:
 >
 > 137 fields with at least about seventy medium text field.
 >
 > The table only contains about 500 rows and will never grow substantially (well, it would be nice (they are members).
 >
 > I could have created a dozen or so tables, which would have been a lot sexier from a database design perspective, but as I am a PHP
 > newby, I did not like that idea.
 >
 > Should I be worried about speed? and if so, how I can I measure speed.
 >
 > I know it all depends.
 >
 > I use a standard ISP package (not a dedicated server). I expect only a handful users of the website at the same time. These large
 > tables are read by a page that displays all the information (address, characteristics, prices, etc...) from all 136 fields. No
 > difficult queries are used at all.
 >
 > I would love to hear any experiences that you may have had that can help me.
 >
 > - Nicolaas
 >
 >
 >> Stay informed about: speed 

I noticed that Message-ID: from
windandwaves contained the following:

 >Because it will can't just go on. I mean, it will not be user friendly to have more than 25 facilities (i mean to read) and
 >certainly not more than 35 so that forces us to rewrite rather than redo the database.

But that's the whole point. Get it right in the first place and you
don't have to redo the database.
 >
 >When i started to do database design I was more into normalisation and the like. Without ever reading anything about it, I totally
 >understood the concept and I went to the Xth degree to make the data as compact as possible. These days, I take a more casual
 >approach.

I won't say I haven't done it. I've put comma separated lists into
fields and used PHP to explode them into an array. Whatever works for
you.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs <a rel="nofollow" style='text-decoration: none;' href="http://www.ckdog.co.uk/rfdmaker/" target="_blank">http://www.ckdog.co.uk/rfdmaker/</a>
 >> Stay informed about: speed 

I noticed that Message-ID: from
windandwaves contained the following:

 >In this particular instance. We are talking about 500 B&Bs, each with their own pieces of information. For example, each B&B has a
 >list of about 15 facilities. About 10-20% of them are repeated, so I could put it in a table like this:
 >
 >Main table
 > unique facilities list
 > table with link between main table and facilities
 >
 >That would be the proper way to do it. But all the extra SQL, in my opinion, in this instant, with only 10-20% repeat, does not
 >weigh up against the joy of having the fields F1 to F25 in the main table.

Until you need field 26

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs <a rel="nofollow" style='text-decoration: none;' href="http://www.ckdog.co.uk/rfdmaker/" target="_blank">http://www.ckdog.co.uk/rfdmaker/</a>
 >> Stay informed about: speed 

"Geoff Berrow" wrote in message

 > windandwaves contained the following:
 >
  >>In this particular instance. We are talking about 500 B&Bs, each with their own pieces of information. For example, each B&B has
  >>a
  >>list of about 15 facilities. About 10-20% of them are repeated, so I could put it in a table like this:
  >>
  >>Main table
  >> unique facilities list
  >> table with link between main table and facilities
  >>
  >>That would be the proper way to do it. But all the extra SQL, in my opinion, in this instant, with only 10-20% repeat, does not
  >>weigh up against the joy of having the fields F1 to F25 in the main table.
 >
 > Until you need field 26
 >
 > --
 > Geoff Berrow (put thecat out to email)
 > It's only Usenet, no one dies.
 > My opinions, not the committee's, mine.
<font color=purple> > Simple RFDs <a rel="nofollow" style='text-decoration: none;' href="http://www.ckdog.co.uk/rfdmaker/</font" target="_blank">http://www.ckdog.co.uk/rfdmaker/</font</a>>

lol - YES - I already ran into that situation! I may add another ten, but that will be it.

Because it will can't just go on. I mean, it will not be user friendly to have more than 25 facilities (i mean to read) and
certainly not more than 35 so that forces us to rewrite rather than redo the database.

When i started to do database design I was more into normalisation and the like. Without ever reading anything about it, I totally
understood the concept and I went to the Xth degree to make the data as compact as possible. These days, I take a more casual
approach.

- Nicolaas
 >> Stay informed about: speed 

"windandwaves" wrote in message

 >


 > [....]
  > > here is my 2 cents on this issue. I think a PHP programer must at least
be somewhat well versed in database design. (personal
  > > opining) Being
 >
 > I am, it is the PHP that I lack

Something isn't clicking here for me.
Wouldn't mysql execute it's query in the same amount of time had the query
come from another front end? I mean, isn't MySQL independent of the program
submiting the query, so whether you use PHP or ASP or whatever, it would
execute at the same rate, correct? I'm just wondering if your asking if a
PHP front-end would execute faster or slower than whatever you use now? I'm
not 100% on this, I have just assumed it was true.

That makes me wonder, if MySQL does in fact execute at the same speed
regardless of the front-end, which front-end would execute the query and be
able to return the results fastest? Hopefully some of these smart people in
here can help me out on this one!

--ScareCrowe
 >> Stay informed about: speed 

windandwaves wrote:
 > I totally understand your point above. I usually make supersleek databases with not a dot of repeated info. However, you can go too
 > far in normalisation.
 >
In that case sorry for my remark about learning about db design. You are
certainly right wanting to balance different design considerations. I
would say: try the simpelest thing that could possebly work, then
refactor when required (You ar right, i'm advocating XP, but i do think
their claim that it saves a lot of time is true IFF you DO refactor to
keep your code flexible

Greetings, succes,

Henk Verhoeven.
 >> Stay informed about: speed 

"ScareCrowe" wrote in message
[...]
 > Something isn't clicking here for me.
 > Wouldn't mysql execute it's query in the same amount of time had the query
 > come from another front end? I mean, isn't MySQL independent of the program
 > submiting the query, so whether you use PHP or ASP or whatever, it would
 > execute at the same rate, correct? I'm just wondering if your asking if a
 > PHP front-end would execute faster or slower than whatever you use now? I'm
 > not 100% on this, I have just assumed it was true.

well, what I am asking is if I can get away with bad database design. MySql seems really fast, but I was wondering at what time you
start to see it slow down... (also keeping in mind that PHP has to work with all the data).

 > That makes me wonder, if MySQL does in fact execute at the same speed
 > regardless of the front-end, which front-end would execute the query and be
 > able to return the results fastest? Hopefully some of these smart people in
 > here can help me out on this one!

I am sure that all of them are pretty similar. Anyway, you should look at the language as a whole, not just the sql retrieval, i
guess.

 > --ScareCrowe
 >
 >
 >> Stay informed about: speed 

"Henk Verhoeven" wrote in message
 > windandwaves wrote:
  >> I totally understand your point above. I usually make supersleek databases with not a dot of repeated info. However, you can go
  >> too far in normalisation.
  >>
 > In that case sorry for my remark about learning about db design. You are certainly right wanting to balance different design
 > considerations. I would say: try the simpelest thing that could possebly work, then refactor when required (You ar right, i'm
 > advocating XP, but i do think their claim that it saves a lot of time is true IFF you DO refactor to keep your code flexible
 >
 > Greetings, succes,
 >
 > Henk Verhoeven.
 >

Hoi henk

yes, that seems to be the most sensible approach to take.

Cheers

- Nicolaas
 >> Stay informed about: speed 

windandwaves wrote:

 > Hi Folks
 >
 > I am putting together a couple of tables in PHP/MySql that are rather
 > large:
 >
 > 137 fields with at least about seventy medium text field.
 >
 > The table only contains about 500 rows and will never grow substantially
 > (well, it would be nice (they are members).

500 rows? That's bugger all, dont worry about it.

Your project will get easier if you learn more about sql btw.

Read up on JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN and if using Postgresql
learn about subqeuries e.g.

select * from files where fileid in (select userid from users where
usertype!='Admin');

bye

--
Fletch
A: Because it disrupts the natural flow when reading
Q: Why is it bad to top post?
 >> Stay informed about: speed