Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If the joins are slow, it may be because the tables did not have <code>FOREIGN KEY</code> relationships and indexes properly defined. If the tables had been properly normalized before, it is probably <em>not</em> a good idea to denormalize them into a single table <strong>unless they were not performant <em>with</em> proper indexing</strong>. <code>FOREIGN KEY</code> constraints require indexing on both the PK table and the related FK column, so simply defining those constraints if they don't already exist may go a long way toward improving performance.</p> <p>The first course of action is to make sure the table relationships are defined correctly and the tables are indexed, before you begin denormalizing it.</p> <p>There is a concept called <a href="http://en.wikipedia.org/wiki/Materialized_view" rel="nofollow"><em>materialized views</em></a>, which serve as a sort of cache for views or queries whose result sets are deterministic, by storing the results of a view's query into a temporary table. MySQL does not support materialized views directly, but you can implement them by occasionally selecting all rows from a multi-table query and storing the output into a table. When the data in that table is stale, you overwrite it with a new rowset. For simple <code>SELECT</code> queries which are used to display data <strong>that doesn't change often</strong>, you may be able to speed up your pageloads using this method. It is not advisable to use it for data which is constantly changing though. </p> <p>A good use for materialized views might be constructing rows to populate your site's dropdown lists or to store the result of complicated reports which are only run once a week. A bad use for them would be to store customer order information, which requires timely access.</p>
    singulars
    1. This table or related slice is empty.
    1. POMySQL Best Practice for adding columns
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMichael Berkowski
    1. USMichael Berkowski
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMySQL Best Practice for adding columns
      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
    1. COThis is a bunch of good information. They do not have good foreign key relationships, but I can't go in and change millions of rows to get them to work together. (At least with my minimal knowledge.) If there were proper foreign key relationships, would the speed be comparable with what I'm doing? Also what is PK/FK? (I assume FK is foreign key column.) I am indeed using the de-normalized table for such a purpose as materialized views. The pages querying the de-normalized table never change (both page and table data).
      singulars
      1. PO
      1. USvictoroux
    2. CO@victoroux PK = primary key, FK = foreign key. If the child table has a nullable column that references a key in the parent table, as long as there isn't spurious data in that column which doesnt exist in the parent table, you can try to create a FK constraint on it. If there are properly indexes and constraints defined, it will almost certainly be a lot faster -- can't say though whether it would match the performance of your single table.
      singulars
      1. PO
      1. USMichael Berkowski
    3. COOkay, I'll work on that. Thank you! Gave you the up and accept
      singulars
      1. PO
      1. USvictoroux
 

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