blocks|key|5041268|text|这里有两种选择(第二种选择由@paul在上面的评论中提出)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5041269|其一，您可以对domain列中的数据进行预处理，使其只具有确切的域而没有其他内容。这是一个众所周知的问题，在Java或JavaScript中相对容易处理.如果这样做了，那么您可以在domain列上添加一个索引，并使用一个WHERE子句，如下所示：|offset|length|style|CODE|5041270|WHERE+domain+IN+('shetlandfoods.co.uk',
+++++++++++++++++'alac.shetland.co.uk',
+++++++++++++++++'malakofflimited.co.uk',
+++++++++++++++++...)|code-block|syntax|javascript|5041271|另一种选择可能是将域的反向与原始WHERE子句中的条件相反进行比较。|5041272|WHERE+REVERSE(domain)+LIKE+'ku.oc.sdoofdnaltehs%25'+OR
++++++REVERSE(domain)+LIKE+'ku.oc.dnaltehs.cala%25'+OR
++++++REVERSE(domain)+LIKE+'ku.oc.detimilffokalam%25'+OR
++++++...|5041273|您甚至可以从应用程序/UI层存储domain的反向，这样就不必强迫MySQL为WHERE子句中的每个项计算反向。|5041274|我可能倾向于第一种选择，假设您在访问MySQL之前有足够的带宽来提取域。|5041275|entityMap^0|0|7|6|2I|6|32|5|0|0|G|5|0|0|G|6|13|5|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@$D|Y|E|Z|F|G]|$D|10|E|11|F|G]|$D|12|E|13|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|14|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|15|8|@$D|16|E|17|F|G]]|9|@]|A|$]]|$1|O|3|P|5|J|7|18|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|19|8|@$D|1A|E|1B|F|G]|$D|1C|E|1D|F|G]]|9|@]|A|$]]|$1|S|3|T|5|6|7|1E|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|V|$]]

Here are two options which come to mind (the second suggested by @paul above in his comment).

One is that you could pre process the data in the <code>domain</code> column such that it only has the exact domain and nothing else. This is a well-known problem and is relatively easy to handle in Java or JavaScript. If this were done, then you can put an index on the <code>domain</code> column and use a <code>WHERE</code> clause looking something like the following:

<pre><code>WHERE domain IN ('shetlandfoods.co.uk',
 'alac.shetland.co.uk',
 'malakofflimited.co.uk',
 ...)
</code></pre>

Another option might be to compare the reverse of the domain against the reverse of the terms you had in your original <code>WHERE</code> clause, e.g.

<pre><code>WHERE REVERSE(domain) LIKE 'ku.oc.sdoofdnaltehs%' OR
 REVERSE(domain) LIKE 'ku.oc.dnaltehs.cala%' OR
 REVERSE(domain) LIKE 'ku.oc.detimilffokalam%' OR
 ...
</code></pre>

You could even store the reverse of the <code>domain</code> from your app/UI layer so that you don't have to force MySQL to compute the reverse for each term in the <code>WHERE</code> clause.

I might lean towards the first option, assuming you have the bandwidth to extract out the domains before hitting MySQL.

blocks|key|3359768|text|您可以为这种场景创建临时表，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3359769|CREATE+TEMPORARY+TABLE+domain_values+(
++domain_value+VARCHAR(100)
);|code-block|syntax|javascript|3359770|然后插入所有域如下：|3359771|INSERT+INTO+domain_values++VALUES+('%25shetlandfoods.co.uk'),+('%25shopshetlandtoday.co.uk'),+........;|3359772|然后选择如下：|3359773|SELECT+e.employee_id+FROM+employee+e+JOIN+domain_values+d+ON+(e.domain+LIKE+d.domain_value)++AND
+++++(e.job_title+LIKE+'%25Manager%25'+OR+e.job_title+LIKE+'%25Director%25')+AND+(e.job_title+NOT+LIKE+'%25Assistant%25');|3359774|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

You can create temp table as follows for this kind of scenario:

<pre><code>CREATE TEMPORARY TABLE domain_values (
 domain_value VARCHAR(100)
);
</code></pre>

Then insert all domains as:

<pre><code>INSERT INTO domain_values VALUES ('%shetlandfoods.co.uk'), ('%shopshetlandtoday.co.uk'), ........;
</code></pre>

Then select as:

<pre><code>SELECT e.employee_id FROM employee e JOIN domain_values d ON (e.domain LIKE d.domain_value) AND
 (e.job_title LIKE '%Manager%' OR e.job_title LIKE '%Director%') AND (e.job_title NOT LIKE '%Assistant%');
</code></pre>

blocks|key|5041302|text|倒转文本，如|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5041303|WHERE+REVERSE(domain)+LIKE+'ku.oc.sdoofdnaltehs%25'+OR+++++++REVERSE(domain)+LIKE+'ku.oc.dnaltehs.cala%25'+OR+++++++REVERSE(domain)+LIKE+'ku.oc.detimilffokalam%25'...|offset|length|style|CODE|5041304|是一种选择，你可以尝试另一种--你也可以试试|5041305|SELECT+employee_id+FROM+employee+WHERE+INSTR(+domain,'shetlandfoods.co.uk'+)+>+0|5041306|这比LIKE+%25text%25快|5041307|entityMap^0|0|0|4G|0|0|0|28|0|2|B|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@$D|R|E|S|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@$D|V|E|W|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|X|8|@$D|Y|E|Z|F|G]]|9|@]|A|$]]|$1|N|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|O|$]]

reversing the text like 

<code>WHERE REVERSE(domain) LIKE 'ku.oc.sdoofdnaltehs%' OR
 REVERSE(domain) LIKE 'ku.oc.dnaltehs.cala%' OR
 REVERSE(domain) LIKE 'ku.oc.detimilffokalam%'...</code> 

is one option you can try another you can also try this 

<code>SELECT employee_id FROM employee WHERE INSTR( domain,'shetlandfoods.co.uk' ) &gt; 0</code>

this is faster then <code>LIKE %text%</code>

I have a table of employees (roughly 4.5 million) which have columns called <code>job_title</code> and <code>domain</code>.

I wish to be able to create and run dynamic queries to select specific employees based on the job title and whose domain is also within the array of domains provided.

How it works, is a user on the front-end ends up with a big array of domains (anywhere from 200 - 10,000+), they then enter two things: 

<ol>
<li>Which job titles they want included</li>
<li>Which job titles they want excluded</li>
</ol>

So we then build a query that ends up looking like this:

<pre><code>SELECT employee_id
FROM employee
WHERE (
 domain LIKE '%shetlandfoods.co.uk' 
 OR domain LIKE '%example1.co.uk' 
 OR domain LIKE '%example2.co.uk'

 -- About 50 additional domains in this list

 OR domain LIKE '%example50.co.uk'
 ) 
 AND (job_title LIKE '%Manager%' OR job_title LIKE '%Director%') 
 AND (job_title NOT LIKE '%Assistant%')
</code></pre>

(Note that the list of domains is extremely long, and may include thousands of domains(!))

Now, a query like this takes around 230 seconds, and that's only with around 180 domains! Imagine using thousands; it'd take forever.

I was wondering if there's any way I can optimize/change this query to make it run a bit faster? Or is there anything I can do to the database?

How can I improve my select query to make it faster?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个员工表(大约450万)，其中有名为job_title和domain的列。我希望能够创建并运行动态查询，以便根据职务名称选择特定的员工，并且其域也位于所提供的域数组中。它是如何工作的，是前端的用户最终得到一个大的域数组(从200到10,000+)，然后输入两样东西：他们想要的职位包括他们希望排除的哪个职位因此，我...

问如何改进select查询，使其更快？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何改进select查询，使其更快？EN