I have a MySQL database that I decided to purge by removing obsolete data to save space and to improve performance by removing unnecessary data. To my surprise, after deleting many records and optimizing the tables, the performance has degraded. I kept a copy of the old database so I can get performance comparisons. I am running a query three times with different filters and I find the old huge database completes the queries in 1.2 seconds (with my php profiler active) while the new purged one takes 5.7 seconds to run the exact same queries. The query is:

```
select org_members.id
from member_calendar, days, org_members
where org_members.org_id=1
  and days.date="2017-07-02"
  and days.removed=0
  and org_members.removed=0
  and member_calendar.day_id=days.id
  and member_calendar.org_member_id=org_members.id;

```

or

```
select  org_members.id
    from  org_members
    INNER JOIN  member_calendar  ON member_calendar.org_member_id=org_members.id
    INNER JOIN  days  ON member_calendar.day_id=days.id
    where  org_members.org_id=1
      and  days.date="2017-07-02"
      and  days.removed=0
      and  org_members.removed=0 

```

I used phpMyAdmin to profile the query in both dB s and the major change was that the sending data step went from 617 microseconds in the large dB to 2.2 seconds in the purged one! The other steps were incrementally longer also, but this was the huge difference.

Using explain on the query the large dB showed a different sequence from the smaller one. The two results results show that:

* the larger table orders the tables member\_calendar bringing up 35943 rows without using keys and then org\_members using the primary key yielding 1 row and then days using the primary key yielding 1 row.
* the purged table query is run by first getting the org\_members without using the primary key and yielding 288 rows and then the member\_calendar without keys yielding 32695 rows and then days using the primary key and yielding 1 row.

So, it seems like the dB has chosen a different way to run the query which is much less efficient. The database threw away the use of the primary key in the org\_members table when executing the query that filters on the primary key with the smaller database but uses it with the larger one. Any ideas on why it would do that and how to correct? Is this a general type problem that should be addressed when constructing queries???? I'm quite surprised by these results. 

I am at a loss for what to do next to improve the situation on the new database. Any suggestions would be hugely appreciated.