Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If you use a single server, I don't think there is a problem with how big the table gets. It isn't just the number of records in a table, but how frequently it is accessed.</p> <p>To manage large datasets, you can use multiple servers. In this case:</p> <ul> <li><p>You can keep all workorders in a single table, and mirror them across different servers (so that you have slave servers)</p></li> <li><p>You can shard the workorders table by boss (in this case you access the server depending on where the workorder belongs) - search for database sharding for more information</p></li> </ul> <p>Which option you choose depends on how you will use your database.</p> <hr> <h2>Mirrors (master/slave)</h2> <p>Keeping all workorders in a single table is good for querying when you don't know which boss a workorder belongs to, eg. if you are searching by product type, but any boss can have orders in any product type.</p> <p>However, you have to store a copy of everything on every mirror. In addition only one server (the master) can deal with update (or adding workorder) SQL requests. This is fine if most of your SQL queries are SELECT queries.</p> <hr> <h2>Sharding</h2> <p>The advantage of sharding is that you don't have to store a copy of the record on every mirror server.</p> <p>However, if you are searching workorders by some attribute for any boss, you would have to query every server to check every shard.</p> <hr> <h2>How to choose</h2> <p>In summary, use a single table if you can have all sorts of queries, including browsing workorders by an attribute (other than which boss it belongs to), and you are likely to have more SELECT (read) queries than write queries.</p> <p>Use shards if you can have write queries on the same order of magnitude as read queries, and/or you want to save memory, and queries searching by other attributes (not boss) are rare.</p> <hr> <h2>Keeping queries fast</h2> <p>Large databases are not really a big problem, if they are not overwhelmed by queries, because they can keep most of the database on hard disk, and only keep what was accessed recently in cache (on memory).</p> <p>The other important thing to prevent any single query from running slowly is to make sure you add the right index for each query you might perform to avoid linear searches. This is to allow the database to binary search for the record(s) required.</p> <p>If you need to maintain a count of records, whether of the whole table, or by attribute (category or boss), then keep counter caches.</p> <h2>When to get a new server</h2> <p>There isn't really a single number you can assign to determine when a new server is needed because there are too many variables. This decision can be made by looking at how fast queries are performing, and the CPU/memory usage of your server.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to handle user's data in MySQL/PHP, for large number of users and data entries
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USronalchn
    1. USronalchn
    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. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIn regards to counter caches, as you say, basically you would create another table where the value added one to it each time (i.e. a log table)?
      singulars
      1. PO
      1. USShackrock
    2. COYes, that's correct (decide if you want to do it in a transaction - more accurate, or if you don't mind if it is approximate - possibly with a recount at times). Also you should check if the database type already has a counter cache for you - MyISAM has a counter cache for the whole table I believe, whereas transactional databases like innoDB or postgreSQL tend to implement counts by traversing each entry.
      singulars
      1. PO
      1. USronalchn
 

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