Databases
StackOverflow2013
Postsdbo
POIs it possible there is a faster way to perform this SELECT query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIs it possible there is a faster way to perform this SELECT query?
    primarykey
    data
    text
    <p><strong>UPDATE (based on everyone's responses):</strong></p> <p>I'm thinking of changing my structure so that I have a new table called prx_tags_sportsitems. I will be removing prx_lists entirely. prx_tags_sportsitems will act as a reference of ID's table to replace the prx_lists.ListString which used to be storing the ID's of tags belonging to each prx_sportsitem.</p> <p>The new relation will be like so:</p> <ul> <li>prx_tags_sportsitems.TagID &lt;--> prx_tags.ID</li> <li>prx_sportsitems.ID &lt;--> prx_tags_sportsitems.OwnerID</li> </ul> <p>prx_tags will contain the TagName. This is so I can still maintain each "tag" as a separate unique entity.</p> <p>My new query for finding all sportsitems with the tag "aerobic" will become something similar to as follows:</p> <pre><code>SELECT prx_sportsitems.* FROM prx_sportsitems, prx_tags_sportsitems WHERE prx_tags_sportsitems.OwnerID = prx_sportsitems.ID AND prx_tags_sportsitems.TagID = (SELECT ID FROM prx_tags WHERE TagName = 'aerobic') ORDER BY prx_sportsitems.DateAdded DESC LIMIT 0,30; </code></pre> <p>Or perhaps I can do something with the "IN" clause, but I'm unsure about that just yet.</p> <p>Before I go ahead with this huge modification to my scripts, does everyone approve? comments? Many thanks!</p> <p><strong>ORIGINAL POST:</strong></p> <p>When it comes to MYSQL queries, I'm rather novice. When I originally designed my database I did something, rather silly, because it was the only solution I could find. Now I'm finding it appears to be causing too much stress of my MYSQL server since it takes 0.2 seconds to perform each of these queries where I believe it could be more like 0.02 seconds if it was a better query (or table design if it comes to it!). I want to avoid needing to rebuild my entire site structure since it's deeply designed the way it currently is, so I'm hoping there's a faster mysql query possible.</p> <p>I have three tables in my database:</p> <ol> <li>Sports Items Table</li> <li>Tags Table</li> <li>Lists Table</li> </ol> <p>Each sports item has multiple tag names (categories) assigned to them. Each "tag" is stored as a separate result in prx_tags. I create a "list" in prx_lists for the sports item in prx_sportsitems and link them through prx_lists.OwnerID which links to prx_sportsitems.ID</p> <p>This is my current query (which finds all sports items which have the tag 'aerobic'):</p> <pre><code>SELECT prx_sportsitems.* FROM prx_sportsitems, prx_lists WHERE prx_lists.ListString LIKE (CONCAT('%',(SELECT prx_tags.ID FROM prx_tags WHERE prx_tags.TagName = 'aerobic' limit 0,1),'#%')) AND prx_lists.ListType = 'Tags-SportsItems' AND prx_lists.OwnerID = prx_sportsitems.ID ORDER BY prx_sportsitems.DateAdded DESC LIMIT 0,30 </code></pre> <p>To help clarify more, the list that contains all of the tag ids is inside a single field called ListString and I structure it like so: " #1 #2 #3 #4 #5" ...and from that, the above query "concats" the prx_tags.ID which tagname is 'aerobic'.</p> <p>My thoughts are that, there probably isn't a faster query existing and that I need to simply accept I need to do something simpler, such as putting all the Tags in a list, directly inside prx_sportsitems in a new field called "TagsList" and then I can simply run a query which does Select * from prx_sportsitems Where TagsList LIKE '%aerobic%' - however, I want to avoid needing to redesign my entire site. I'm really regretting not looking into optimization beforehand :(</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJoe
    1. USJoe
    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
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POIs it possible there is a faster way to perform this SELECT query?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POIs it possible there is a faster way to perform this SELECT query?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POIs it possible there is a faster way to perform this SELECT query?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWait . . . You're concatenating the ID numbers for tags, and storing all those id numbers in a single string in prx_lists.ListString? Is that right?
      singulars
      1. POIs it possible there is a faster way to perform this SELECT query?
      1. USMike Sherrill 'Cat Recall'
    2. COYes, that's correct.
      singulars
      1. POIs it possible there is a faster way to perform this SELECT query?
      1. USJoe
    3. COPS - still waiting for someone to review my update? thanks
      singulars
      1. POIs it possible there is a faster way to perform this SELECT query?
      1. USJoe
 

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