如何使用mysql限制单词而不是定义 [英] How to limit words but not definitions using mysql

问题描述

我有两个相关的表，分别称为单词和定义.每个表有超过250,000行.这是我的表格设置方式.

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

单词

id word
 1 book
 2 love
 3 hate
 4 smile
 and it goes on.

定义

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.

我写了一个php，它使用以下命令从单词中选择5个结果:
$query = "SELECT id, word FROM words LIMIT 5";

I wrote a php that select 5 results from words using:
$query = "SELECT id, word FROM words LIMIT 5";

然后我循环播放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.

所以，我加入了"SELECT * FROM words JOIN definition ON words.id = wordid LIMIT 5"之类的表.

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

现在，查询仅提取5个结果，而我不能将单词限制为5行.

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

我的目标是以非常快速的方式提取5个单词以及它们的许多定义.也请记住我想要的结果数组格式.我将如何处理?非常感谢您的帮助.

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

这篇关于如何使用mysql限制单词而不是定义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！