使用Like / match的慢速mysql查询

I need to search for the closest Dialcode bigger than the actual one. For example the closest from 33 will be 331 or 332 (if 331 doesn't exist)... So it has to be 33xxxx, 34 is not valid.

我需要搜索比实际更大的Dialcode。例如，最接近33的将是331或332（如果331不存在）......所以它必须是33xxxx，34无效。

These 2 queries work but are too slow (250ms/row):

这两个查询有效，但速度太慢（250毫秒/行）：

SELECT Dialcode
FROM table
WHERE (Dialcode LIKE '$var%' AND Dialcode > '$var' AND Price IS NOT NULL)
ORDER BY Dialcode ASC LIMIT 1

SELECT Dialcode
FROM table
WHERE (MATCH(Dialcode) AGAINST ('$var*' IN BOOLEAN MODE) AND Dialcode > '$var'
      AND Price IS NOT NULL)
ORDER BY Dialcode ASC LIMIT 1

My Dialcode is a PRIMARY KEY BIGINT(15).

我的拨号码是一个主要的大键（15）。

Is I do this, it's really fast (>1ms/row) but it's not exactly what I need:

我这样做，它真的很快（> 1ms /行）但它不是我需要的：

SELECT Dialcode
FROM table
WHERE (Dialcode >= '$var' AND Price IS NOT NULL)
ORDER BY Dialcode ASC LIMIT 1

So I guess my problem is the LIKE / MATCH AGAINST.

所以我想我的问题就是喜欢/匹配。

Any ideas would be appreciated.

任何想法，将不胜感激。

UPDATE solution:

更新解决方案：

Solution adapted from raina77ow suggestion:

解决方案改编自raina77ow建议：

SELECT Dialcode FROM table WHERE (( (Dialcode BETWEEN $var * 1 AND ’9’ )
   OR (Dialcode BETWEEN $var * 10 AND $var.’99’ )   
   OR (Dialcode BETWEEN $var * 100 AND $var.’999’ )
OR (Dialcode BETWEEN $var * 1000 AND $var.’9999’ )
…
) AND Price IS NOT NULL) ORDER BY Dialcode ASC LIMIT 1

Thanks guys!

多谢你们！

2 个解决方案

#1

I see that the main problem here is that index is built upon the numeric values of Dialcode - not the string ones. So both queries do not use index at all.

我看到这里的主要问题是索引建立在Dialcode的数值上 - 而不是字符串。因此，两个查询都根本不使用索引。

You can try to build a numeric function, the problem is that this function will have Dialcode in the left part of evaluation - so the index again won't be used.

你可以尝试构建一个数字函数，问题是这个函数在评估的左边部分会有Dialcode - 所以不会再使用索引。

Perhaps this approach might be more useful.

也许这种方法可能更有用。

SELECT Dialcode 
FROM table 
WHERE ( (Dialcode BETWEEN %value% * 10 AND (%value%*10 + 9) 
   OR (Dialcode BETWEEN %value% * 100 AND (%value%*100 + 99) 
   OR (Dialcode BETWEEN %value% * 1000 AND (%value%*1000 + 999)
   ...
) AND Price IS NOT NULL
ORDER BY Dialcode LIMIT 1;

It's ugly as hell (I even wrote this wrong the first two times - alfasin should get the credit for correcting me), yes, but it should hit the index. The alternative is using UNION (and not OR), but I suppose both queries will be executed the same way (it's not verified, though).

这很丑陋（我甚至在前两次写错了 - alfasin应该得到纠正我的功劳），是的，但它应该打到索引。另一种方法是使用UNION（而不是OR），但我认为两个查询都将以相同的方式执行（尽管没有验证）。

#2

Try using regex, something like:

尝试使用正则表达式，如：

SELECT Dialcode
FROM table
WHERE Dialcode REGEXP '^[$var]'
ORDER BY Dialcode ASC LIMIT 1

I can't test it right now, but I believe it should work.

我现在无法测试它，但我相信它应该可行。

#1