0

The query is:

SELECT T.ID, T.A, T.T 
FROM  table1 AS T
WHERE (userID='333335') AND (category LIKE '1%') AND (T.T='You Ain\'t Much 
Fun' AND T.A='KEITH, TOBY') GROUP BY T.A, T.T

I have a BTREE index over 2 columns - userID, and another column which doesn't feature in the above query. The index is set up with userID being the first column.

The table is innoDB, and the total table size is around 70 million, but the rows that userID='333335' equals total only about 500,000.

If userID has an index on it, and that's the first column queried, shouldn't the rest of the query be able to breeze through 500,000 rows pretty quickly?

What is it that I'm overlooking please?

Thank you for your time and help.

Improve this question
7
  • 1
    Please provide SHOW CREATE TABLE; if category is numeric, there is a problem. What is the PRIMARY KEY?
    – Rick James
    Commented Jun 16, 2019 at 2:26
  • Why GROUP BY a,t when you also test for equality on those columns?
    – Rick James
    Commented Jun 16, 2019 at 2:28
  • Hi Rick. category is int(8) unsigned NOT NULL, and the PRIMARY KEY is the row ID - int(10) unsigned NOT NULL AUTO_INCREMENT. re the GROUP BY, it's about 6 years since I wrote the code, but I think the idea was to eliminate duplicates, as some DJs have duplicate songs by the same artist, but made by a different karaoke manufacturer - eg "The Gambler, Kenny Rogers, SF11284", "The Gambler, Kenny Rogers, ZOOM35353", but for this query, I only want to display "The Gambler, Kenny Rogers" once in the results.
    – Shaun
    Commented Jun 16, 2019 at 3:10
  • Then what is the purpose of T.ID? There would be two of them, and the GROUP BY would deliver one of them at random. Perhaps you want GROUP_CONCAT(T.ID)?
    – Rick James
    Commented Jun 16, 2019 at 4:00
  • 2
    category LIKE '1%' requires converting every row's category from INT to VARCHAR, then check for stating with 1. Note that that implies 1, 10,11,...,19, 100,101,...,199, etc. Did you really need to be checking for that??
    – Rick James
    Commented Jun 16, 2019 at 22:00

1 Answer 1

Reset to default
3

Your index may be finding the records relatively quickly, but it has to perform a double lookup for every single one of those rows that it finds for that userId.

Try adding the following index:

(userId, A, T, category)

to table1.

This will be a covering index, and should yield extremely quick results.

Improve this answer
7
  • Thank you Willem. Would the index you suggested work the same if I made it... (userID, appRowID, A, T, category) ? The current index is (userID, appRowID), so I want to know if I could replace it with your index to service the query issue I posted here AND the queries that (userID, appRowID) is designed to use. I ask as I'm trying to keep the number of indexes on this table low, as re-indexing after writes takes a long time with it being so large. Most of the operations are reads, but I still want to account for the writes. Thank you
    – Shaun
    Commented Jun 16, 2019 at 2:27
  • 1
    @Shaun Unfortunately you cannot use the index you suggest to cover both types of queries. When an index is used, it uses each column left to right. If a value isn't specified for a column, that is the limit of the index's usefulness in terms of filtering results. Depending on what other indexes and queries occur it may be possible to condense some other indexes, but there isn't enough information for me to suggest anything. You can open another question and provide SHOW CREATE TABLE statement along with the queries you need to cover and then we can help find any index savings.
    – Willem Renzema
    Commented Jun 16, 2019 at 2:47
  • Thank you Willam. I'm trying to limit the size of any new index, as when DJs upload their song lists in 5000 song blocks (some have 200,000+ songs), the indexing required between each song block used to take a long time before I condensed & minimised my indexes to what they are now. So I'm wondering... if I change the query to "SELECT T.ID, T.A, T.T FROM table1 AS T WHERE userID='333335' AND T.T='You Ain\'t Much Fun' AND T.A='KEITH, TOBY' AND category LIKE '1%' GROUP BY T.A, T.T" and set a new index of just (userId, T), will that index be used by MySQL for the newly designed query? Thank you.
    – Shaun
    Commented Jun 18, 2019 at 5:04
  • @Shaun It should be used, and it should be an improvement. However, it will still require a double lookup on all the matches rows. The improvement will come from the fact that, in theory, fewer rows will be matched against this new index. How much it helps will vary a lot based on how few results are found for userId and T together.
    – Willem Renzema
    Commented Jun 18, 2019 at 5:33
  • @Shaun I recommend reading this answer I wrote for another question, it may help you understand better how indexes work, and so gain a better sense for yourself on how they provide benefit: dba.stackexchange.com/a/229071/24505
    – Willem Renzema
    Commented Jun 18, 2019 at 5:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.