Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This is a nice question, if rather broad (and none the worse for that).<br> If I understand you, then you're asking how to attack the problem of optimisation starting from scratch. </p> <p>The first question to ask is: "<em>is there a performance problem?</em>"<br> If there is no problem, then you're done. This is often the case. Nice. </p> <p>On the other hand... </p> <h3>Determine Frequent Queries</h3> <p><strong>Logging</strong> will get you your frequent queries.<br> If you're using some kind of data access layer, then it might be simple to add code to log all queries.<br> It is also a good idea to log when the query was executed and how long each query takes. This can give you an idea of where the problems are.<br> Also, <strong>ask the users</strong> which bits annoy them. If a slow response doesn't annoy the user, then it doesn't matter. </p> <h3>Select the optimization factors?</h3> <p>(I may be misunderstanding this part of the question) You're looking for any patterns in the queries / response times.<br> These will typically be queries over large tables or queries which join many tables in a single query. ... but if you log response times, you can be guided by those. </p> <h3>Types of changes one can make?</h3> <p>You're specifically asking about optimising tables.<br> Here are some of the things you can look for: </p> <ul> <li><strong>Denormalisation</strong>. This brings several tables together into one wider table, so in stead of your query joining several tables together, you can just read one table. This is a very common and powerful technique. NB. I advise keeping the original normalised tables and building the denormalised table in addition - this way, you're not throwing anything away. How you keep it up to date is another question. You might use triggers on the underlying tables, or run a refresh process periodically. </li> <li><strong>Normalisation</strong>. This is not often considered to be an optimisation process, but it is in 2 cases: <ul> <li>updates. Normalisation makes updates much faster because each update is the smallest it can be (you are updating the smallest - in terms of columns and rows - possible table. This is almost the very definition of normalisation. </li> <li>Querying a denormalised table to get information which exists on a much smaller (fewer rows) table may be causing a problem. In this case, store the normalised table as well as the denormalised one (see above). </li> </ul></li> <li><strong>Horizontal partitionning</strong>. This means making tables smaller by putting some rows in another, identical table. A common use case is to have all of this month's rows in table <em>ThisMonthSales</em>, and all older rows in table <em>OldSales</em>, where both tables have an identical schema. If most queries are for recent data, this strategy can mean that 99% of all queries are only looking at 1% of the data - a huge performance win. </li> <li><strong>Vertical partitionning</strong>. This is Chopping fields off a table and putting them in a new table which is joinned back to the main table by the primary key. This can be useful for very wide tables (e.g. with dozens of fields), and may possibly help if tables are sparsely populated. </li> <li><strong>Indeces</strong>. I'm not sure if your quesion covers these, but there are plenty of other answers on SO concerning the use of indeces. A good way to find a case for an index is: find a slow query. look at the query plan and find a table scan. Index fields on that table so as to remove the table scan. I can write more on this if required - leave a comment. </li> </ul> <p>You might also like <a href="https://stackoverflow.com/questions/18783/sql-what-are-your-favorite-performance-tricks#103176">my post on this</a>. </p>
    singulars
    1. This table or related slice is empty.
    1. POHow do you optimize tables for specific queries?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USAJ.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow do you optimize tables for specific queries?
      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. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    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