0

I have a table in MySQL 5.7.13, that has 3 columns (actually more than that, but these three are used for searching) - it has a column company_id, it has a column department_id, and it has column status.

Now, usually employees work like this, they enter the system, they choose the company they work with, and they choose the department (it's obligatory). After that they go to menu, and can either get data from table without specifying status, or they can also specify, what status do they want?

So it seemed natural for me to use complex index (company_id, department_id, status), due to the prefix order of mysql index. Since they always filter by company and department, and sometimes by status, everything seemed fine.

However, recently, a new feature is added. There are also special employees, who, unlike usual users, select only company, but not the department. So, their query filters by company and status. And that means that the index breaks, it will only filter by company, not by status, because the department is omitted.

What is a workaround about this? I see two solutions here. One is that status is very restricted - it has basically for values - CREATED(1), SENT(2), ACCEPTED(3), REJECTED(4). So, I can create index (company_id, status, department_id), and if the user does not choose any status, I can append in query something like this:

AND status in (1,2,3,4)

Will index work efficiently for that? Or, other solution is to create the second index and have both (company_id, department_id, status) and (company_id, status, department_id), but will the mysql optimizer choose the correct index? I am not sure, which way is better, and will any of them work.

Fge
  • 2,971
  • 4
  • 23
  • 37
SPIRiT_1984
  • 2,717
  • 3
  • 29
  • 46
  • How many rows in the table? What is a typical number of rows for one company? What's the max? Is there only one `status` for a `company+dept`? – Rick James Jul 30 '16 at 01:11

1 Answers1

0

From what I can see, this should work fine. The optimizer will utilize an index on (x,y,z) composite and perform a using index; using where if needed. Note, it is assuming my understanding that the left-most part of the composite is already part of your where clause, such as x allowing y to already be in the mix for index use.

On another note, in cases where that IN clause is huge even, and say on a single column index, the IN will be speedily satisfied. See the answer I wrote up here regarding that: Index uses IN.

Naturally, look at the MySQL Manual Page entitled EXPLAIN Syntax and something like Using EXPLAIN to Write Better MySQL Queries.

Community
  • 1
  • 1
Drew
  • 24,851
  • 10
  • 43
  • 78