CODEWITHSUNDEEP
mysqlindexingquery-optimization
Bilal
Bilal

Reputation: 429

Three columns index

I have the following table:

questions (id, title, user_id, ask_id (nullable), created_at)

i have the following three queries:

  1. select * from questions where user_id = ? order by created_at desc;
  2. select * from questions where user_id = ? and ask_id is not null order by created_at desc;
  3. select * from questions where ask_id = ? order by created_at desc;

Whats the best way to optimize those queries?
Is it possible to use a single index that would help avoid using file sort?

Upvotes: 0

Views: 29

Answers (2)

Rick James
Rick James

Reputation: 142208

INDEX(user_id, created_at) -- for #1
INDEX(ask_id, created_at) -- for #3

The first one will help somewhat for #2.

"Filesort" is not as evil as everyone makes out. It is merely a clue that you either don't have the best index, or that the query is complex enough to require one or more sorts.

If this is a large table and you don't really use id, there are other optimizations I could suggest.

Upvotes: 0

Emmanuel
Emmanuel

Reputation: 58

No. Create two different indexes for user_id and ask_id

Upvotes: 1

Related Questions