php - How to speed up my mysql JOIN query? -

i developing 1 social chatting application. in app having 5000 users. want fetch username last 1 hour in online.

i have 2 tables users , messages. database heavy. users table having 4983 records , messages table having approximately 15 millions records. want show 20 users user sending message between last 1 hour.

my query -

select a.username,a.id users join messages b   a.id != ".$getuser['id']." ,   a.is_active=1 ,   a.is_online=1 ,   a.id=b.user_id ,   b.created > date_sub(now(), interval 1 hour)       group b.user_id       order b.id desc limit 20 

users table -

messages table -

above query working fine. query getting slow. , times page hanged out. want faster record.

note - $getuser['id'] login user id.

any idea?

you can use indexes

a database index data structure improves speed of operations in table. indexes can created using 1 or more columns, providing basis both rapid random lookups , efficient ordering of access records.

while creating index, should considered columns used make sql queries , create 1 or more indexes on columns.

practically, indexes type of tables, keep primary key or index field , pointer each record actual table.

the users cannot see indexes, used speed queries , used database search engine locate records fast.

insert , update statements take more time on tables having indexes select statements become fast on tables. reason while doing insert or update, database need insert or update index values well.

simple , unique index:

you can create unique index on table. unique index means 2 rows cannot have same index value. here syntax create index on table

create unique index index_name on table_name ( column1, column2,...); 

you can use 1 or more columns create index. example, can create index on tutorials_tbl using tutorial_author.

create unique index author_index on tutorials_tbl (tutorial_author) 

you can create simple index on table. omit unique keyword query create simple index. simple index allows duplicate values in table.

if want index values in column in descending order, can add reserved word desc after column name.

mysql> create unique index author_index on tutorials_tbl (tutorial_author desc) 

alter command add , drop index: there 4 types of statements adding indexes table:

alter table tbl_name add primary key (column_list): statement adds primary key, means indexed values must unique , cannot null.

alter table tbl_name add unique index_name (column_list): statement creates index values must unique (with exception of null values, may appear multiple times).

alter table tbl_name add index index_name (column_list): adds ordinary index in value may appear more once.

alter table tbl_name add fulltext index_name (column_list): creates special fulltext index used text-searching purposes.

here example add index in existing table.

mysql> alter table testalter_tbl add index (c); 

you can drop index using drop clause along alter command. try out following example drop above-created index.

mysql> alter table testalter_tbl drop index (c); 

you can drop index using drop clause along alter command. try out following example drop above-created index.

alter command add , drop primary key: 

you can add primary key in same way. make sure primary key works on columns, not null.

here example add primary key in existing table. make column not null first , add primary key.

mysql> alter table testalter_tbl modify int not null; mysql> alter table testalter_tbl add primary key (i); 

you can use alter command drop primary key follows:

mysql> alter table testalter_tbl drop primary key; 

to drop index not primary key, must specify index name.

displaying index information: can use show index command list out indexes associated table. vertical-format output (specified \g) useful statement, avoid long line wraparound:

try out following example:

mysql> show index table_name\g