Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>See <a href="http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html" rel="noreferrer">How MySQL Uses Indexes</a>.</p> <p>Also validate whether MySQL still performs a <a href="http://dev.mysql.com/doc/refman/5.1/en/how-to-avoid-table-scan.html" rel="noreferrer">full table scan</a> after you add an additional 2000-or-so rows to your <code>user_metrics</code> table. In small tables, access-by-index is actually more expensive (I/O-wise) than a table scan, and MySQL's optimizer might take this into account.</p> <p><em>Contrary to my previous post</em>, it turns out that MySQL is also <a href="http://forge.mysql.com/wiki/MySQL_Internals_Optimizer" rel="noreferrer">using a cost-based optimizer</a>, which is very good news - that is, provided you run your <code>ANALYZE</code> at least once when you believe that the volume of data in your database is <em>representative</em> of future day-to-day usage. </p> <p>When dealing with cost-based optimizers (Oracle, Postgres, etc.), you need to make sure to periodically run <a href="http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html" rel="noreferrer"><code>ANALYZE</code></a> on your various tables as their size increases by more than 10-15%. (Postgres will do this automatically for you, by default, whereas other RDBMSs will leave this responsibility to a DBA, i.e. you.) Through statistical analysis, <code>ANALYZE</code> will help the optimizer get a better idea of how much I/O (and other associated resources, such as CPU, needed e.g. for sorting) will be involved when choosing between various candidate execution plans. Failure to run <code>ANALYZE</code> may result in very poor, sometimes disastrous planning decisions (e.g. millisecond-queries taking, sometimes, hours because of <em>bad nested loops</em> on <code>JOIN</code>s.)</p> <p>If performance is still unsatisfactory after running <code>ANALYZE</code>, then you will typically be able to work around the issue by using hints, e.g. <code>FORCE INDEX</code>, whereas in other cases you might have stumbled over a MySQL bug (e.g. this <a href="http://bugs.mysql.com/bug.php?id=28612" rel="noreferrer">older one</a>, which could have bitten you were you to use Rails' <code>nested_set</code>).</p> <p>Now, <strong>since you are in a Rails app</strong>, it will be cumbersome (and defeat the purpose of <code>ActiveRecord</code>) to issue your custom queries with hints instead of continuing to use the <code>ActiveRecord</code>-generated ones.</p> <p>I had mentioned that in our Rails application <em>all</em> <code>SELECT</code> queries dropped below 100ms after switching to Postgres, whereas some of the complex joins generated by <code>ActiveRecord</code> would occasionally take as much as 15s or more with MySQL 5.1 because of nested loops with inner table scans, even when indices were available. No optimizer is perfect, and you should be aware of the options. Other potential performance issues to be aware of, besides query plan optimization, are locking. This is outside the scope of your problem though.</p>
    singulars
    1. This table or related slice is empty.
    1. POMySQL not using indexes with WHERE IN clause?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USTim Cooper
    1. USvladr
    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
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks Vlad. I'm hoping to resolve this w/o too much pain with our current setup, but I appreciate hearing about your success w/ Postgres.
      singulars
      1. PO
      1. USjasonlong
    2. COI second Postgres. It is an excellent database.
      singulars
      1. PO
      1. USLuke Francl
    3. COHi, blackant, have you made any progress with this MySQL issue?
      singulars
      1. PO
      1. USvladr
 

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