Mysql optimization - displaying the last 10 records, but also identifying duplicate rows

Question

I am new to mysql and for several days I was pulling my hair for this problem. I need to improve / optimize this request so that it works faster - now it takes more than 5 seconds.

Here is the request:

SELECT SQL_NO_CACHE COUNT(*) as multiple, a.*,b.*  
FROM announcements as a  
INNER JOIN stores as s  
ON a.username=s.username
    WHERE s.username is not null AND s.state='NC' 
GROUP BY a.announcement_id
ORDER BY a.dt DESC LIMIT 0,10

The table of stores consists of: store_id, username, name, state, city, zip, etc.

The announcement table consists of: announce_id, msg, dt, username

The store table contains about 10,000 entries, and the announcement table contains about 500,000 entries.

, , - 10 . , , ( ). , , , "Chipotle" , , , " ". count (*) group by, , count(*) > 1, , .

, . SQL_NO_CACHE, , , ?

, . , " " . , , , - .

-

DESC;

Field       Type            Null    Key     Default         Extra  
store_id    int(11)         NO      PRI     NULL            auto_increment  
username    varchar(20)     NO      MUL     NULL       
name        varchar(100)    NO              NULL       
street      varchar(100)    NO              NULL       
city        varchar(50)     NO              NULL       
state       varchar(2)      NO              NULL       
zip         varchar(15)     NO              NULL      

DESC;

Field              Type           Null      Key     Default     Extra
dt                 datetime       NO                NULL     
username           varchar(20)    NO        MUL     NULL     
msg                varchar(200)   NO                NULL     
announcement_id    int(11)        NO        PRI     NULL        auto_increment

EXPLAIN ;

id  select_type     table   type    possible_keys   key       key_len     ref         rows     Extra
1   SIMPLE          a       index   username        PRIMARY   47          NULL        315001   Using temporary; Using filesort
1   SIMPLE          b       ref     username        username  62          a.username  1        Using where

Answer 1

- :

SELECT SQL_NO_CACHE COUNT(*) as multiple, a.*,b.*   
FROM announcements as a   
INNER JOIN 
(
  SELECT username, COUNT(username) as multiple FROM stores
  WHERE username IS NOT NULL AND state = 'NC'
  GROUP BY username
 )  as s 
ON a.username=s.username 
ORDER BY a.dt DESC LIMIT 10 

Answer 2

dt, , MySQL , , (, )

announcements.dt - MySQL .

Answer 3

• JOIN, MySQL , . , JOIN, .
  EXPLAIN , , , .
• dt ( unixtime )
• If possible, create an integer user ID for each username and JOIN using this column (add an index to this number).
• Not sure if MySQL still has problems with this, but replacing COUNT (*) with COUNT (1) can be useful.