inner join large table with small table , how to speed up

dear php and mysql expertor i have two table one large for posts artices 200,000records (index colume: sid) , and one small table (index colume topicid ) for topics has 20 record .. have same topicid

curent im using : ( it took round 0.4s)

+do get last 50 record from table:

SELECT sid, aid, title, time, topic, informant, ihome, alanguage, counter, type, images, chainid FROM veryzoo_stories ORDER BY sid DESC LIMIT 0,50

+then do while loop in each records for find the maching name of topic in each post:

while ( .. ) { SELECT topicname FROM veryzoo_topics WHERE topicid='$topic'" .... }

+Now I going to use Inner Join for speed up process but as my test it took much longer from 1.5s up to 3.5s

SELECT a.sid, a.aid, a.title, a.time, a.topic, a.informant, a.ihome, a.alanguage, a.counter, a.type, a.images, a.chainid, t.topicname FROM veryzoo_stories a INNER JOIN veryzoo_topics t ON a.topic = t.topicid ORDER BY sid DESC LIMIT 开发者_运维百科0,50

It look like the inner join do all joining 200k records from two table fist then limit result at 50 .. that took long time..

Please help to point me right way doing this.. eg take last 50 records from table one.. then join it to table 2 .. ect

Do not use inner join unless the two tables share the same primary key, or you'll get duplicate values (and of course a slower query).

Please try this :

SELECT * 
FROM (
SELECT a.sid, a.aid, a.title, a.time, a.topic, a.informant, a.ihome, a.alanguage, a.counter, a.type, a.images, a.chainid
FROM veryzoo_stories a
ORDER BY sid DESC 
LIMIT 0 , 50
)b
INNER JOIN veryzoo_topics t ON b.topic = t.topicid

I made a small test and it seems to be faster. It uses a subquery (nested query) to first select the 50 records and then join.

Also make sure that veryzoo_stories.sid, veryzoo_stories.topic and veryzoo_topics.topicid are indexes (and that the relation exists if you use InnoDB). It should improve the performance.

Now it leaves the problem of the ORDER BY LIMIT. It is heavy because it orders the 200,000 records before selecting. I guess it's necessary. The indexes are very important when using ORDER BY.

Here is an article on the problem : ORDER BY … LIMIT Performance Optimization

I'm just give test to nested query + inner join and suprised that performace increase much: it now took only 0.22s . Here is my query:

SELECT a.*, t.topicname 
FROM (SELECT sid, aid, title, TIME, topic, informant, ihome, alanguage, counter, TYPE, images, chainid 
  FROM  veryzoo_stories 
  ORDER BY sid DESC 
  LIMIT 0, 50) a 
  INNER JOIN veryzoo_topics t ON a.topic = t.topicid 

if no more solution come up , i may use this one .. thanks for anyone look at this post