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

bd96500e110b49cbb3cd949968f18be7.png

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值