MySQL中限制词_如何使用mysql限制单词而不是定义

I have two related tables called words and definitions. Each table has over 250,000 rows. Here's how my tables are setup.

Words

id word

1 book

2 love

3 hate

4 smile

and it goes on.

Definitions

id wordid pos definition

1 1 v to enter in a book or list; record; register.

2 1 n a book, a bible

3 2 n noun definition of love

4 2 v verb definition of love

and it goes on.

I wrote a php that select 5 results from words using:

$query = "SELECT id, word FROM words LIMIT 5";

And then I loop each of the 5 results like

foreach($results as $row){

$wordid = $row['id'];

$word = $row['word'];

// I really dont want to do this way

$definitions_query = "SELECT * FROM definition WHERE wordid = $wordid";

// After the query

foreach($definitions_result as $row1){

$definition = $row1['definition'];

$pos = $row1['pos'];

// Desired Results

$data[$word][$pos][] = $definition;

}

}

My code accomplished the mission, but it's too slow.

So, I joined the tables like "SELECT * FROM words JOIN definition ON words.id = wordid LIMIT 5".

Now, the query only pulls five results and I cannot limit the words to 5 rows.

My goal is to pull 5 words along with their many definitions in a very fast way. Please keep in mind of my desired result array format as well. How would I go about it? Your help will be much appreciated.

解决方案

Try this query. It will limit parent data and join the tables

SELECT w.word,d.definition,d.pos FROM

(SELECT id, word FROM words ORDER BY id LIMIT 5) w

JOIN definition d ON d.wordid = w.id