返回一列上具有最高值的行，该列对应于另一列中的给定值

There's a MySQL table named raw_contacts with the following structure:

有一个名为raw_contacts的MySQL表,结构如下:

ID (primary auto-increment, int)
PHONE (composite unique with NAME, varchar)
NAME (composite unique with PHONE, varchar)
FREQUENCY (int)
Composite unique key is named PHONENUM

I am trying to write a query to return the row with the highest corresponding value in the FREQUENCY column for any given value for PHONE. I did find a related post but the accepted answer doesn't work for my case since I have a composite key of two columns to run the check against.

我正在尝试编写一个查询,以便为FONE的任何给定值返回FREQUENCY列中具有最高对应值的行。我确实找到了一个相关的帖子但是接受的答案对我的情况不起作用,因为我有两列的复合键来运行检查。

Just to illustrate, consider the following sample:

仅举例说明,请考虑以下示例:

1 1234 John 6
2 1234 Dave 2
3 2199 Bill 9
4 1878 Dani 3
5 1234 Cory 7
6 1234 Gore 5
7 3319 Cory 1

Run against 1234 on the above sample, the query should return the 5th row since Cory has the highest count in the FREQUENCY column for that number.

在上面的示例中针对1234运行,查询应该返回第5行,因为Cory在该数字的FREQUENCY列中具有最高计数。

Here's what I've come up with and it works great:

这就是我想出来的,它很有效:

select RC.ID, RC.PHONE, RC.FREQUENCY, RC.NAME
from `raw_contacts` RC
inner join(
    select PHONE, max(FREQUENCY) FREQUENCY
    from `raw_contacts`
    where PHONE="11111"
    group by PHONE
) RC2 on RC.PHONE = RC2.PHONE and RC.FREQUENCY = RC2.FREQUENCY
limit 1

Is there any more resource-friendly query for this task since it needs to run at a very high frequency on a table with millions of records? I'm looking to optimize it to the bone!

对于此任务是否有任何更加资源友好的查询,因为它需要在具有数百万条记录的表上以非常高的频率运行?我正在寻求优化骨骼!

P.S. In case of more than one rows qualifying, I need only one of them, which one doesn't matter.

附:如果有多行合格,我只需要其中一行,哪一行无关紧要。

1 个解决方案

#1

join is a costly operation in DB and I think you must avoid using it as much as possible!!! I suggest to use the following query and compare its result with your one.

加入是一个昂贵的DB操作,我认为你必须尽可能避免使用它!我建议使用以下查询并将其结果与您的结果进行比较。

select * from `raw_contacts` RC1 where PHONE="11111" 
and FREQUENCY>=all (select FREQUENCY from `raw_contacts` RC2 where RC2.PHONE=RC1.PHONE) LIMIT 1

also you can consider different types of indexing (a good toturial is here) on your table to speedup special and more frequent queries

你也可以在你的桌子上考虑不同类型的索引(这里有一个很好的推文)来加速特殊和更频繁的查询

#1