Databases
StackOverflow2013
Postsdbo
PODatabase scalability: Which is more important, size of table or number of queries?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODatabase scalability: Which is more important, size of table or number of queries?
    primarykey
    data
    text
    <p>I'll take a simplified StackOverflow system as an example.</p> <p>Although limiting some features, it would be possibly to hold Questions and Answers in the same table:</p> <pre><code>(Django-esque pseudo-code) QA table: parent = ForeignKey(self) category = ForeignKey(Category) title = CharField() description = TextField() </code></pre> <p>Then, to get the Questions and Answers for Question with ID 1, an SQL SELECT would be done for <code>id==1</code> or <code>parent==1</code>. The downfall would be that the <code>tags</code> and <code>title</code> fields aren't used by Answers</p> <p>The alternative of course would be two tables:</p> <pre><code>Questions: category = ForeignKey(Category) title = CharField() description = TextField() Answers: parent = ForeignKey(Questions) description = TextField() </code></pre> <p>Which would require two queries to get the Questions and Answers.</p> <p>Instinct says the former is a horrible idea but I'm not sure why.</p> <p>Which is faster and more scalable?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USbcoughlan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODatabase scalability: Which is more important, size of table or number of queries?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODatabase scalability: Which is more important, size of table or number of queries?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThere is a lot more to scalability than either of these factors. Load on server, number of concurrent client, indexing strategy, caching strategy and many more factors go into it. This question is not really answerable.
      singulars
      1. PODatabase scalability: Which is more important, size of table or number of queries?
      1. USOded
    2. COThe answer in this case is most likely going to be "it depends on which database + version + indexes you are using (+ how much memory is available to your database) - so profile and find out". Both structures only require a single query to pull questions and answers, and both select statements *can* be done with a join.
      singulars
      1. PODatabase scalability: Which is more important, size of table or number of queries?
      1. USSean Vieira
    3. CO"The downfall would be that the tags and title fields aren't used by Answers" -- Yes. **So don't do that.** Start with a "good" normalized database design. The problems will come, as will the solutions. Starting with a "good base" will reduce the number of problems that need to be dealt with and increase the chance that the solutions can focus entirely on the real problem and not something needlessly introduced. (The size of the table is only terribly relevant if indexes' *can't* "select out" the subset of rows actually required for query.)
      singulars
      1. PODatabase scalability: Which is more important, size of table or number of queries?
      1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload