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