StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The main thing to keep in mind when doing a search through a string is that you want to limit the length of the string you are searching through. Right now, you have multiple path+filename values tucked into a single row-column pair - as I've mentioned above, this is poorly normalized (and is part of the reason you're having trouble doing lookups).</p> <p>Given that you can't really change the schema of the table you're having trouble with, a better alternative might be creating a structure to work with the metadata that describes the files stored within a certain row.</p> <p>For example, one option might be to create a table that contains <code>filename</code>-<code>rowID</code> pairs, where each row of the original table is linked to the parsed-out filenames within the <code>TEXT</code>column of that row. That gives you the option of limiting your search by first doing a lookup on a shorter string (the <code>filename</code>), and then using that constraint to help search a smaller number of rows to satisfy the path+filename combination and achieve a unique result.</p> <p>If you have a large number of files with identical names, another option might be to implement a hash index, using <code>rowID</code>s from your original table and a hash of each path+filename from that row using <code>CHECKSUM()</code> or whatever hashing function you have available.</p> <p>Using an 'indexing' table like this one does add overhead: you have to maintain the metadata as the original table gets updated, but it also means you're doing your heavy lifting ahead of time and making future queries of the data much faster.</p>

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