Jump to content


Photo

Best Practice Question


  • Please log in to reply
2 replies to this topic

#1 Saragon

Saragon
  • New Members
  • Pip
  • Newbie
  • 9 posts

Posted 29 June 2006 - 12:49 PM

I'm building a mySQL database for a property manager's website -- a much larger database than my previous projects. (Not Oracle-sized by any means, but still new to me.) I never played around with VIEWs much before now, but I'm wondering whether or not they might speed things up.

To browse through the properties, I'm going to use the standard ten-at-a-time list, with certain pieces of information (rent, street, thumbnail image, etc.) displayed for each one. There's also a large table for amenities that are mostly boolean values (fireplace?, pets allowed?, cable?, and so forth). Each of these pages will see a lot of use.

Which is the better practice: To pull the data directly from the base tables via PHP and have the appropriate function control what's shown? Or have a SQL VIEW already in place that contains the data, and have my code put the entire row into an array or something similar?

#2 obsidian

obsidian
  • Staff Alumni
  • Advanced Member
  • 3,202 posts
  • LocationSeattle, WA

Posted 29 June 2006 - 01:01 PM

[!--quoteo(post=389234:date=Jun 29 2006, 08:49 AM:name=Saragon)--][div class=\'quotetop\']QUOTE(Saragon @ Jun 29 2006, 08:49 AM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I'm building a mySQL database for a property manager's website -- a much larger database than my previous projects. (Not Oracle-sized by any means, but still new to me.) I never played around with VIEWs much before now, but I'm wondering whether or not they might speed things up.

To browse through the properties, I'm going to use the standard ten-at-a-time list, with certain pieces of information (rent, street, thumbnail image, etc.) displayed for each one. There's also a large table for amenities that are mostly boolean values (fireplace?, pets allowed?, cable?, and so forth). Each of these pages will see a lot of use.

Which is the better practice: To pull the data directly from the base tables via PHP and have the appropriate function control what's shown? Or have a SQL VIEW already in place that contains the data, and have my code put the entire row into an array or something similar?
[/quote]

for something like this, can you just use a SQL join to pull them all into one query anyway? when it's only joining 2-3 tables, you may be just a well off to write a more complex query that will simply return all the values in one row that you can then assign into an array as you would be to create a view. now, if you're having to join 6-7 tables with some very intricate relations, i'd definitely recommend creating a view, but i think you're borderline with your situation.
You can't win, you can't lose, you can't break even... you can't even get out of the game.

<?php
while (count($life->getQuestions()) > 0)
{   $life->study(); } ?>
  LINKS: PHP: Manual MySQL: Manual PostgreSQL: Manual (X)HTML: Validate It! CSS: A List Apart | IE bug fixes | Zen Garden | Validate It! JavaScript: Reference Cards RegEx: Everything RegEx

#3 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 July 2006 - 02:17 AM

Agreed... VIEWs, IMHO, are only good for very complicated & resource-intensive queries you don't want screwed up, or for "faking" row-level permissions.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users