Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>There's not a great general solution to the question "How big is too big" - such concerns are frequently dependent on what you're doing with your data and what your performance considerations are.</p> <p>There are some fundamental limits on table sizes. You can't have more than 1000 columns. Your records can't be bigger than 8k each. These limits change depending on database engine. (The ones here are for InnoDB.)</p> <p>It sounds like you've merged several different data sets into one table. You probably have some fields that tell you what data set this record pertains to, along with some data fields, and some timestamp information. That's not a very wide record (unless you're logging, say, all the input parameters of each request.) Your main problem will be with <strong>selectivity</strong>. Indexing this table in a meaningful way will be a challenge. If your common fields can be selective enough that you can use them to get to the records you want without consulting the table, that will be a huge plus. (Cf. table scan)</p> <p>For that many records per day (basically, two a second all day, and I'm presuming you have a peak-load period where it's much higher), you'll also want to make sure that you specifically look at optimizations on <strong>improving insertion speed</strong>. As a general rule, more indexes = slower insertions. If you can, consider archiving off outdated records to another table entirely. In prior workplaces, we've used an archival strategy of Last Month, Prior Three Months, Prior Six Months, each in separate tables. Another idea is to delete older records. Many environments simply don't need information beyond a certain date. Hanging on to logging records from three months ago is often overly expensive.</p> <p>Finally, don't neglect the <strong>physical storage</strong> of your table. The thinner your records are, the less physical IO needs to occur to read (or for that matter, to insert) a record. You can store your indexes on a separate physical hard drive. If there's a lot of redundant data in your records storing the table compressed might actually be a speed increase. If you have a little cash to burn, consider the value of a good RAID array for striping your data.</p> <p>So, to answer your basic question: it's a lot of records, but with a careful eye towards tuning, it won't be a problem.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow big is too big for a MySQL table?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USJohn LeBoeuf-Little
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow big is too big for a MySQL table?
      singulars
      1. PTQuestion
    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. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for all the information. So you are saying 6 million a table should be no problem if I take care of all the other details you mentioned?
      singulars
      1. PO
      1. USNathan
    2. COI'm saying it's manageable if you're careful to think about all these things. Performance is unlikely to be really great, but it'll be good enough.
      singulars
      1. PO
      1. USJohn LeBoeuf-Little
 

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