How can I get all messages from a user without looping over the entire table? [closed]

I have a database with many Users and many many Messages, each with a single User sender and a single User recipient. I want to get all messages sent by John from my MySQL table with a low lagtime. He has only ever sent 20 messages, but the total messages sent by the database amount to a full 20,000,000. How can I quickly pull John’s sent messages?

I have tried:

SELECT * FROM Messages WHERE name = 'John';

which times out.

I am considering making Views for every username, so I can just SELECT * FROM JohnView;. This would presumably be satisfactory time-wise, but I don’t want to make unique views for all 500,000 users. Is there a way to pre-sort a table by name so I can binary search for all messages from any individual user?

Schema:

CREATE TABLE Users(
    Name varchar(255);
);
CREATE TABLE Messages(
    Id int auto_increment primary key;
    Content TEXT;
);

Creating a view will not help at all with performance, you can think of a view as a stored query. In my experience, MySQL does not use views in an optimal way, so the result may very well be worse than without the view.

Other vendors (but not MySQL AFAIK) have a special kind of view known as Materialized View (or Materialized Query Table). Since there is a cost of maintaining them during modification of the base tables, they are typically used in non-OLTP scenarios. Yet another special kind of view is a Statistical View which can help in estimating the cardinality of JOINs. However, none of these apply to your scenario as far as I can tell.

Do you have an index on the name? If not you should create an index like:

CREATE INDEX ... ON Messages (name);

There may be other queries with additional predicates if so, you could include those columns in the index as well