Databases
StackOverflow2013
Postsdbo
POHow to optimise database search
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to optimise database search
    primarykey
    data
    text
    <p>I have the following problem.</p> <p>I have a database of e.g. 1000 items. Each item can have any number of identifying tags associated with it. For purpose of question, the item and tags are purely hypothetical. So for instance, say one of the items is a DVD, then the tags for that item would be: DVD, The Lone Ranger, western, action, family</p> <p>And another DVD is tagged with: DVD, The Magnificent 7, western, action</p> <p>Now someone on my website searches for the following key words in the search box and clicks Search: western, action, family, PG13</p> <p>Both DVD's match at least 2 of the search terms, and none match the PG13. Also the first DVD's match is closest to the search terms.</p> <p>The search is started and for all 1000 products I have to search through each items tags to see if they match the search criteria.</p> <p>So For the first DVD, it matches 3 of the 4 tags, and for the second DVD it matches 2 of the 4 tags.</p> <p>My question is, how to I optimise this search? For each item, the query looks through each items tags, then match it to the search terms. When no items matching all search terms are found, it has to "drop" one of the search terms and look to see if any item matches any 3 combinations of the 4 search terms.</p> <p>Then it drops another search term and searches for 2 of the 4 search terms, trying to match any 2 combination of the 4 search terms.</p> <p>It is the "dropping" of search terms and searching all possible combinations that I need to optimise. Does anyone know what the best algorithm for this would be, or can anyone provide pseudo code for this?</p> <p>I have no idea on this as each scenario I try to think of I should still have to search each possible combination of search terms which while slow down the speed at which items can be returned to customers. </p> <p>EDIT: I have thought about giving each item tag a weight, but the problem is that the nature of the tags are such that no tag carries more weight than any other tags. All tags are equally weighted/important. </p> <p>The speed that the Database can be queried and results retuned is my biggest goal here.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USheyred
    1. USheyred
    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. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COshow us the query and the tables structures (with index)
      singulars
      1. POHow to optimise database search
      1. USThomas Ruiz
    2. COyou could give a weight to every section you want to search on. Like 'movie name' is more important than 'Genre'. so if you can't find anything with 'Name + Genre', drop Genre. Now, this is the dropping of idenitifiers... for the actual querying ... what about just querying every possible parameter the user gave. If you don't have any matches, do the same query, but drop the Weakest parameter. Still nothing? drop another parameter and query the same query again. This case you only have to query n times, where n = amount of parameters. Which in the end is not really bad.
      singulars
      1. POHow to optimise database search
      1. USViridis
    3. CO(in your example: worst case = 4 queries). Best case, the first query immedietly returns the 'wanted' array of items. (which you potentially might want to verify or validate before using ... depending on what you're doing)
      singulars
      1. POHow to optimise database search
      1. USViridis
 

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