[Solved] Is it possible there is a faster way to perform this SELECT query?
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

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

Database type:

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

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid OFFSET In LIMIT Clause (query line: 17): OFFSET clauses can be very slow when used with high offsets (e.g. with high page numbers when implementing paging). Instead, use the following \u003ca target\u003d"_blank" href\u003d"http://www.eversql.com/faster-pagination-in-mysql-why-order-by-with-limit-and-offset-is-slow/"\u003eseek method\u003c/a\u003e, which provides better and more stable response rates.
  2. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  3. Avoid Subqueries (query line: 9): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  4. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `prx_sportsitems` ADD INDEX `prx_sportsitems_idx_id_dateadded` (`ID`,`DateAdded`);
ALTER TABLE `prx_sportsitems` ADD INDEX `prx_sportsitems_idx_dateadded` (`DateAdded`);
ALTER TABLE `prx_tags` ADD INDEX `prx_tags_idx_tagname` (`TagName`);
ALTER TABLE `prx_tags_sportsitems` ADD INDEX `prx_sportsitems_idx_ownerid` (`OwnerID`);
The optimized query:
SELECT
        prx_sportsitems.* 
    FROM
        prx_sportsitems,
        prx_tags_sportsitems 
    WHERE
        prx_tags_sportsitems.OwnerID = prx_sportsitems.ID 
        AND prx_tags_sportsitems.TagID = (
            SELECT
                prx_tags.ID 
            FROM
                prx_tags 
            WHERE
                prx_tags.TagName = 'aerobic'
        ) 
    ORDER BY
        prx_sportsitems.DateAdded DESC LIMIT 0,
        30

Related Articles



* original question posted on StackOverflow here.