SQL indexing applicability for multiple columns

Question

I have a table in my database as users, resources and user_resources. users and resources tables connect as many to many relationship and user_resources is the connecting table.

Users table is as below

Suppose I create an index for columns age, gender, diagnosis, symptoms and treatments columns, as for my knowledge that index is applicable for the below search queries as well

SELECT * FROM Users WHERE age=12
SELECT * FROM Users WHERE age=12 AND gender='Female'

and I know that this index is not applicable for the queries like below

SELECT * FROM Users WHERE gender='Female' and age=12

I want to know if this index will be applicable for below type of queries as well

SELECT * FROM Users WHERE age=12 and treatments='treatment1'
SELECT * FROM Users WHERE gender='Female' OR diagnosis='diagnosis1'

since the indexes with multiple columns is applicable with leftmost columns. Further, if I join Users table with Resources table joining user_resources table but with same where clauses will it be applicable for the index. As an example

    SELECT ur.userId, ur.resourceId, u.symptoms
    FROM Users u
    JOIN User_Resources ur ON u.userId = ur.userId
    JOIN Resources r On ur.resourceId = r.resourceId
    WHERE u.age = 12 AND
          u.diagnosis like '%symptom1%'

I expect that @Strawberry means, in a certain practical sense--age changes on birthday anniversary--although not age as of an event. Of course that can be ignored if you are just using arbitrary column names to ask about indexing. — philipxy, Dec 21 '19 at 05:30
How is this not a duplicate & unresearched? How does the online manual not answer this? Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS The DBMS can see that `gender='Female' and age=12` can use the index. — philipxy, Dec 21 '19 at 05:39
Does this answer your question? [MySQL indexes - what are the best practices?](https://stackoverflow.com/questions/3049283/mysql-indexes-what-are-the-best-practices) — philipxy, Dec 21 '19 at 05:46
@philipxy I went through that question before posting this question and many documentations about indexing and posted this question since none of them did not answer my specific question. No one would waste time by posting questions if there is already questions answered for their specific question. MySQL indexes - what are the best practices? is a more general problem and that does not answer my question — shamila, Dec 23 '19 at 04:24
"No one would waste time by posting questions if there is already questions answered for their specific question." That is unfortunately very wrong. — philipxy, Dec 23 '19 at 08:42

score 1 · Accepted Answer · answered Dec 12 '19 at 12:10

1

Consider the table a(id,x,y,z) with a primary key on id and a composite index formed on (x,y,z).

SELECT * FROM a WHERE x = 1 AND y = 3 can use the first two columns of the index

SELECT * FROM a WHERE x = 1 AND z = 3 can use the first column of the index only

SELECT * FROM a WHERE y = 3 cannot use the index

SELECT * FROM a WHERE x LIKE '%1%' cannot use the index, because '%...' can never use an index

SELECT * FROM a WHERE x LIKE '1%' can use the index

answered Dec 12 '19 at 12:10

Strawberry

33,750
13
40
57

1

You could mention that `SELECT * FROM a WHERE y=3 AND x = 1` can use the first two columns of the index--since the asker gives an example of index column order being inverted in a condtion but they think that means the DBMS can't uninvert--"and I know that this index is not applicable for the queries like below `SELECT * FROM Users WHERE gender='Female' and age=12`". – philipxy Dec 21 '19 at 05:25

score 1 · Answer 2 · answered Dec 21 '19 at 02:20

INDEX(gender, age)
INDEX(age, diagnosis)

would handle the first 4 queries.

OR makes the 4th one hard to optimize. As does LIKE with a leading wildcard in the last one.

Here's a cookbook on creating indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Is User_Resources is a many-to-many mapping table? If so, follow the advice in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

A side note: If age is the age of humans, then the value will keep changing. You would be better off storing the birthdate and computing the age on the fly instead of running an update every night or every year.

SQL indexing applicability for multiple columns

2 Answers2