Databases
StackOverflow2013
Postsdbo
POIs there anything I can do to optimize this mysql query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs there anything I can do to optimize this mysql query?
    primarykey
    data
    text
    <p>I am hoping some of you who are experts in mysql can help me to optimize my mysql search query...</p> <p>First, some background:</p> <p>I am working on a small exercise mysql application that has a search feature.</p> <p>Each exercise in the database can belong to an arbitrary number of nested categories, and each exercise can also have an arbitrary number of searchtags associated with it.</p> <p>Here is my data structure (simplified for readability)</p> <pre><code>TABLE exercises ID title TABLE searchtags ID title TABLE exerciseSearchtags exerciseID -&gt; exercises.ID searchtagID -&gt; searchtags.ID TABLE categories ID parentID -&gt; ID title TABLE exerciseCategories exerciseID -&gt; exercises.ID categoryID -&gt; categories.ID </code></pre> <p>All tables are InnoDB (no full-text searching).</p> <p>The ID columns for exercises, searchtags and categories have been indexed.</p> <p>"exerciseSearchtags" and "exerciseCategories" are many to many join tables expressing the relationship between exercises and searchtags, and exercises and categories, respectively. Both the exerciseID &amp; searchtagID columns have been indexed in exerciseSearchtags, and both the exerciseID and categoryID columns have indexed in exerciseCategories.</p> <p>Here are some examples of what exercise title, category title and searchtag title data might look like. All three types can have multiple words in the title. </p> <pre><code>Exercises (ID - title) 1 - Concentric Shoulder Internal Rotation in Prone 2 - Straight Leg Raise Dural Mobility (Sural) 3 - Push-Ups Categories (ID - title) 1 - Flexion 2 - Muscles of Mastication 3 - Lumbar Plexus Searchtags (ID - title) 1 - Active Range of Motion 2 - Overhead Press 3 - Impingement </code></pre> <p>Now, on to the search query:</p> <p>The search engine accepts an arbitrary number of user inputted keywords.</p> <p>I would like to rank search results based on the number of keyword/category title matches, keyword/searchtag title matches, and keyword/exercise title matches.</p> <p>To accomplish this, I am using the following dynamically generated SQL:</p> <pre><code> SELECT exercises.ID AS ID, exercises.title AS title, ( // for each keyword, the following // 3 subqueries are generated ( SELECT COUNT(1) FROM categories LEFT JOIN exerciseCategories ON exerciseCategories.categoryID = categories.ID WHERE categories.title RLIKE CONCAT('[[:&lt;:]]',?) AND exerciseCategories.exerciseID = exercises.ID ) + ( SELECT COUNT(1) FROM searchtags LEFT JOIN exerciseSearchtags ON exerciseSearchtags.searchtagID = searchtags.ID WHERE searchtags.title RLIKE CONCAT('[[:&lt;:]]',?) AND exerciseSearchtags.exerciseID = exercises.ID ) + ( SELECT COUNT(1) FROM exercises AS exercises2 WHERE exercises2.title RLIKE CONCAT('[[:&lt;:]]',?) AND exercises2.ID = exercises.ID ) // end subqueries ) AS relevance FROM exercises LEFT JOIN exerciseCategories ON exerciseCategories.exerciseID = exercises.ID LEFT JOIN categories ON categories.ID = exerciseCategories.categoryID LEFT JOIN exerciseSearchtags ON exerciseSearchtags.exerciseID = exercises.ID LEFT JOIN searchtags ON searchtags.ID = exerciseSearchtags.searchtagID WHERE // for each keyword, the following // 3 conditions are generated categories.title RLIKE CONCAT('[[:&lt;:]]',?) OR exercises.title RLIKE CONCAT('[[:&lt;:]]',?) OR searchtags.title RLIKE CONCAT('[[:&lt;:]]',?) // end conditions GROUP BY exercises.ID ORDER BY relevance DESC LIMIT $start, $results </code></pre> <p>All of this works just fine. It returns relevant search results based on user input.</p> <p>However, I am worried that my solution may not scale well. For example, if a user enters a seven keywords search string, that will result in a query with 21 subqueries in the relevance calculation, which might start to slow things down, if the tables get big.</p> <p>Does anyone have any suggestions as to how I can optimize the above? Is there a better way to accomplish what I want? Am I making any glaring errors in the above?</p> <p>Thanks in advance for your help.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USTravis
    1. USTravis
    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
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIs there anything I can do to optimize this mysql query?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POIs there anything I can do to optimize this mysql query?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POIs there anything I can do to optimize this mysql query?
      1. USEmil
      1. VTFavorite
    1. This table or related slice is empty.
 

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