Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009

    Question Unanswered: question about query speed

    Say I need to store information about the types of planes a user has. for each plane, I need to store 6 different numbers (average crew size, fuel used per flight, etc). There may be hundreds of customers, with perhaps no more than 5 to 10 plane types each.

    which would be faster to query-joining a 3 column table (plane_ID, varName,varValue) with X rows per customer, or a 7 column table (plane_ID, aveCrewSize,...) with 1 row per customer?

    Thanks all!

    I've heard that disk-seek per row is the biggest bottleneck, but on small test tables the first was faster than the second, even after the queries were cached

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    Quote Originally Posted by dzdrazil
    ... plane_ID, varName,varValue...
    this is known as EAV -- entity-attribute-value -- and is a dangerous design

    don't do it | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2009

    is it dangerous because it's slow/ sloppy design?

    Here's the main reason why I'm still asking:

    Part of what I'm doing is turning an Excel spreadsheet model into a database for an online application, and the spreadsheet is practically complex enough to be sold as a product on its own. As such, there's a lot of table-within-a-table stuff that's making me want to just put a bajillion tables in the database and use JOINs everywhere (which I understand is also bad for overhead). Would it be better to have a single table of constants or is removing constants not really a beneficial normalization? I'm stuck in MyISAM b/c this is on a shared host, so I don't really have the benefit of foreign keys or such things.

    PS- I saw the Entity-Attribute-Value initially when reading a book on designing a site using the Zend Framework for PHP- I guess books aren't all that reliable after all

  4. #4
    Join Date
    Jan 2009
    Can a user have more than 1 plane?
    Then use 2 tables

    table1 = (userid,planeid)
    table2 = (planeid, 7columns)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts