mysql查询优化 [英] mysql query optimisation

问题描述

我的x表中大约有(总共1,049,906，查询花费了0.0005秒)，

i have around (1,049,906 total, Query took 0.0005 sec) in my x table ,

如果我只是想尝试检索特定的字段记录，

If i simply retrieve trying to retrive the particular field records ,

几乎不需要6分钟，

这是我的查询

SELECT CUSTOMER_CODE FROM X_TBL ;

CUSTOMER_CODE =>唯一

CUSTOMER_CODE => UNIQUE

查询超过6分钟，

为此，请告诉我优化技巧，

Tel me optimization tips for this ,

在某些情况下也要搜索客户，因此我在CUSTOMER_CODE中使用了类似的代码，

Also in some situation to search customer , i used the CUSTOMER_CODE in like ,

从X_TBL中选择"CUSTOMER_CODE"，例如"$ KEY_WORD％"

select CUSTOMER_CODE from X_TBL WHERE CUSTOMER_CODE LIKE "$KEY_WORD%"

是的，我有索引.

如何检查脚本执行时间...在php中，

How to check script execution timing...in php ,

我在下面的代码段中使用了该代码段，但是它总是朝着增加的方向显示.

i have used below snippet but it always showing towards of increasing..

   list ($msec, $sec) = explode(' ', microtime());
$microtime = (float)$msec + (float)$sec;

问候 婆罗尼古玛(Bharanikumar)

Regards Bharanikumar

推荐答案

如果我理解此权利，则您的表中有超过一百万条记录，并且想知道为什么要花6分钟才能从 all 获取CUSTOMER_CODE

If I understand this right, you have more than one million records in a table and wondering why it takes 6 minutes to get CUSTOMER_CODE from all of them at once? There is nothing to "optimize", most likely you need to build different queries something like

SELECT CUSTOMER_CODE FROM X_TBL LIMIT 0, 100;

或

SELECT COUNT(CUSTOMER_CODE) FROM X_TBL;

或其他，取决于您要解决的任务.

or whatever, depending on the task you're trying to solve.

从CUSTOMER_CODE开始搜索时，如果LIKE "$KEY_WORD%"上的LIKE "$KEY_WORD%"简单索引会有所帮助，但同样，请勿尝试一次选择所有记录，除非有特殊情况您真正需要的地方.

As of searching by CUSTOMER_CODE, in case of LIKE "$KEY_WORD%" simple index on CUSTOMER_CODE will help, but again, don't try to select all records at once, except for cases where you do really need this.

这篇关于mysql查询优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！