StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The execution time geometrically proportional to the number of rows in each table rather than arithmetically e.g.</p> <pre><code>3 tables with 10 rows each => 1,000 comparision 3 tables with 10, 10 and 40 rows => 4,000 comparisons 3 tables with 20 rows each => 8,000 comparisons </code></pre> <p>You could probably re-factor the query to avoid some of the joins/cursors - when do you need an answer?</p> <p>Could you do something like this:</p> <pre><code>SELECT precursor_id, feature_table_id FROM MSMS_precursor INNER JOIN ( SELECT mzMin, mzMax, rtMin, rtMax, spectrum_id, feature_table_id, msrun_msrun_id FROM spectrum INNER JOIN (select feature_table_id, mzMin, mzMax, rtMin, rtMax, msrun_msrun_id from feature where feature.msrun_msrun_id = 'value' ) subquery ON subquery.msrun_msrun_id = spectrum.msrun_msrun_id WHERE spectrum.scan_start_time BETWEEN subquery.rtMin AND subquery.rtMax ) subquery ON subquery.spectrum_id = MSMS_precursor.spectrum_spectrum_id WHERE MSMS_precursor.ion_mz BETWEEN subquery.mzMin AND subquery.mzMax </code></pre> <p>Using a subquery enables you to reduce the number of comparisons between the tables - you can quickly filter out the unwanted features, then the un-related spectra before searching for suitable precursors.</p> <p><strong>I don't use SQLLite - but the principle should still apply.</strong></p> <p>UPDATED : fixed bug in SQL</p> <p>Notes:</p> <p>You don't have to worry about the ANDs, you'll only get:</p> <ul> <li>features where feature.msrun_msrun_id = 'value'</li> <li>spectra for those features and where spectrum.scan_start_time BETWEEN subquery.rtMin AND subquery.rtMax</li> <li>precursors for those spectrs and where MSMS_precursor.ion_mz BETWEEN subquery.mzMin AND subquery.mzMax</li> </ul> <p><strong>UPDATE 18/May:</strong></p> <p>It's the indexing!!! you have indexes on the search fields, but not on the fields participating in the joins - foreign key indices really boost performance:</p> <pre><code>CREATE INDEX `fk_msrun_msrun_id_feature` ON `feature` (`msrun_msrun_id` ASC); CREATE INDEX `fk_spectrum_spectrum_id_feature` ON `feature` (`msrun_msrun_id` ASC); CREATE INDEX `fk_spectrum_spectrum_id_MSMS_precursor` ON `MSMS_precursor` (`spectrum_spectrum_id` ASC); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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