Is it possible there is a faster way to perform this SELECT query?

Question

UPDATE (based on everyone's responses):

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.

The new relation will be like so:

• prx_tags_sportsitems.TagID <--> prx_tags.ID
• prx_sportsitems.ID <--> prx_tags_sportsitems.OwnerID

prx_tags will contain the TagName. This is so I can still maintain each "tag" as a separate unique entity.

My new query for finding all sportsitems with the tag "aerobic" will become something similar to as follows:

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;

Or perhaps I can do something with the "IN" clause, but I'm unsure about that just yet.

Before I go ahead with this huge modification to my scripts, does everyone approve? comments? Many thanks!

ORIGINAL POST:

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.

I have three tables in my database:

1. Sports Items Table
2. Tags Table
3. Lists Table

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

This is my current query (which finds all sports items which have the tag 'aerobic'):

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

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'.

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 :(

Answer 1

Whenever I am writing a query, and think I need to use LIKE, an alarm goes off in my head that maybe there is a better design. This is certainly the case here.

You need to redesign the prx_lists tables. From what you've said, its hard to say what the exact schema should be, but here is my best guess:

prx_lists should have three columns: OwnerID, ListType, and TagName. Then you would have one row for each tag an OwnerID has. Your above query would now look something like this:

SELECT prx_sportsitems.*
FROM prx_sportsitems, prx_lists
where prx_lists.TagName = 'aerobic'
      AND prx_lists.OwnerID = prx_sportsitems.ID

This is a MUCH more efficient query. Maybe ListType doesn't belong in that table either, but its hard to say without more info about what that column is used for.

Don't forget to create the appropriate indexes either! This will improve performance.

Refactoring your database schema might be painful, but its seems to me the only way to fix your long term problem.

Answer 2

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'.

There's your problem right there. Don't store delimited data in a DB field (ListString). Modeling data this way is going to make it extremely difficult/impossible to write performant queries against it.

Suggestion: Break the contents of ListString out into a related table with one row for each item.