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

Question

UPDATE (based on all answers):

I am thinking of changing my structure so that I have a new prx_tags_sportsitems table. I will completely remove prx_lists. prx_tags_sportsitems will act as a table ID link to replace prx_lists.ListString, which used to store the tag identifier for each prx_sportsitem.

The new ratio would be:

• prx_tags_sportsitems.TagID ↔ prx_tags.ID
• prx_sportsitems.ID ↔ prx_tags_sportsitems.OwnerID

prx_tags will contain a TagName. This means that I can save each "tag" as a separate unique object.

My new query to search for all sporting events with an aerobic tag will become something like the following:

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 maybe I can do something with the "IN" clause, but so far I'm not sure about that.

Before I continue this huge modification of my scripts, will everyone approve? Comments? Thank you very much!

ORIGINAL MAIL:

When it comes to MYSQL queries, I'm more likely to get started. When I initially created my database, I did something rather stupid, because this is the only solution I could find. Now I find that this causes my MYSQL server to be overstrained, since it takes 0.2 seconds to complete each of these queries, where I believe it could be more than 0.02 seconds if it was the best query (or table design if he comes to him!). I want to avoid the need to rebuild the entire structure of the site, as it is deeply designed as it is now, so I hope a faster mysql query is possible.

I have three tables in my database:

• Sporting goods table
• Tag table
• List table

Each sporting element has several tag names (categories) assigned to it. Each "tag" is saved as a separate result in prx_tags. I create a “list” in prx_lists for the sport item in prx_sportsitems and bind them through prx_lists.OwnerID, which refers to prx_sportsitems.ID

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

 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, a list containing all the tag identifiers is inside a single field called ListString, and I structure it like this: "# 1 # 2 # 3 # 4 # 5" ... and from this, the above query concatenates prx_tags .ID, which is "aerobic".

My thoughts are that there probably isn’t a faster request, and I just have to accept that I need to do something simpler, for example, put all the tags in the list right inside the prx_sportsitems in a new field called "List Tags", and then I can just run a query that makes Select * from prx_sportsitems Where TagsList LIKE '% aerobic%' - however I want to avoid having to redesign my entire site. I'm really sorry that I did not look at the optimization in advance :(

Answer 1

Whenever I write a request and think that I need to use LIKE , there is an alarm in my head, maybe there is a better design. This certainly takes place here.

You need to redo the prx_lists tables. From what you said, it’s hard to say exactly which circuit should be, but here is my best guess:

prx_lists should have three columns: OwnerID , ListType and TagName . Then you will have one line for each tag that the owner identifier has. Now your request will 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. ListType not be part of this table either, but it's hard to say without asking what this column is used for.

Do not forget to create the corresponding indexes ! This will improve performance.

Refactoring your database schema can be painful, but it seems to me that this is the only way to fix your long-term problem.

Answer 2

To help clarify, a list that contains all the tag identifiers inside one field called ListString and I create it like this: "# 1 # 2 # 3 # 4 # 5" ... and from this above the request is "concats" prx_tags.ID, which tag is “aerobic”.

There your problem is right there. Do not store delimited data in the DB field (ListString). Modeling data in this way will make it extremely difficult / impossible to write executable queries to it.

Suggestion: Change the contents of the ListString in the linked table with one row for each item.

Answer 3

• Do not make any changes without looking at the execution plan . (And also post this here by editing your original question.)
• As your LIKE clause built, MySQL cannot use an index.
• LIKE's suggestion is a symptom. Your table structure is more likely.

You will probably get at least one improvement in order by building reasonable tables.

I'm really sorry that I do not watch optimization in advance

This is not what caused your problem. Ignorance of the basics of database design caused your problem. (This is observation, not criticism. You can correct ignorance. You cannot fix stupidity.)

Further

Submit your existing table structure and your proposed changes. You will be much happier with our ability to predict what your code will do than with our ability to predict what your description of part of your code will do.

Answer 4

the list containing all the tag identifiers is located inside one field called ListString, and I structure it like this: "# 1 # 2 # 3 # 4 # 5" ... and from this above the request is "concats" prx_tags.ID, which is tagged is aerobic.

Not only is this bad, but denormalized data is also stored, but the delimiter character is rare.

Temporary improvement

The quickest way to improve the situation is to change the delimiter character you use ("#") to a comma:

 UPDATE PRX_LISTS SET liststring = REPLACE(liststring, '#', ',') 

Then you can use the MySQL FIND_IN_SET function :

  SELECT si.* FROM PRX_SPORTSITEMS si JOIN PRX_LISTS l ON l.ownerid = si.id JOIN PRX_TAGS t ON FIND_IN_SET(t.id, l.liststring) > 0 WHERE t.tagname = 'aerobic' AND l.listtype = 'Tags-SportsItems' ORDER BY si.DateAdded DESC LIMIT 0, 30 

Long term solution

As you have already seen, searching for the specifics of denormalized data does not work very well and makes queries overly complex. You need to modify the PRX_LISTS table PRX_LISTS that one row contains a unique combination of SPORTSITEM.ownerid and PRX_TAGS.id and any other columns you may need. I would recommend renaming, namely lists of what exactly? The name is too general:

 CREATE TABLE SPORTSITEM_TAGS_XREF ( sportsitem_ownerid INT, tag_id INT, PRIMARY KEY (sportsitem_ownerid INT, tag_id) )