需要微调我在varchar列上有索引的mysql查询

I have a table which has nearly 2,000,000 records. I have a column(crmid) which is a varchar in that table. I wrote a query say,

我有一张表有近2,000,000条记录。我有一个列（crmid），它是该表中的varchar。我写了一个查询说，

 Select column1, column2,..... from table where crmid = ?

the value comes from the front-end. Previously it was working fine but now it is getting timed-out from front end. Then I used an index on that column which even doesn't helped me much. After indexing I explained the query, again it is traversing 1,800,000 rows using index too.

价值来自前端。以前它工作正常，但现在它正在从前端超时。然后我在该专栏上使用了一个索引，甚至对我没什么帮助。索引后我解释了查询，同样它也使用索引遍历1,800,000行。

Please help me in fine tuning this query.

请帮我微调这个查询。

Note : This is not the actual query, its just the simplest form. There are many joins in the actual query.

注意：这不是实际查询，它只是最简单的形式。实际查询中有许多连接。

EDITED :: The full query is

EDITED ::完整的查询是

select  column1
        , column2 
from    table1 a
        inner join appointmentstatusmst st 
            on  st.entity_id = a.shdstatuslviid 
        inner join hlcclientdetails 
            on  a.shdclientid = hlcclientdetails.cldregno 
            and a.shdclientdcnid = hlcclientdetails.clddcnid
where   hlcclientdetails.cldunifiedcrmid = ?

The explain output is 需要微调我在varchar列上有索引的mysql查询

解释输出是

1 个解决方案

#1

Is there an index on appointmentstatusmst.entityid? How about on table1.shdclientid and table1.shdclientdcnid? How about on hlcclientdetails.cldregno and hlcclientdetails.clddcnid?

在appointmentstatusmst.entityid上有索引吗？ table1.shdclientid和table1.shdclientdcnid怎么样？在hlcclientdetails.cldregno和hlcclientdetails.clddcnid上怎么样？

For your joins to work smoothly, you need indexes on the columns involved in the join criteria as well as on the column you're using to choose output rows.

为了使联接顺利进行，您需要在连接条件中涉及的列上以及用于选择输出行的列上的索引。

@Bohemian is correct, this is a medium-sized, not large, problem.

@Bohemian是正确的，这是一个中等大小的问题。

EDIT.

编辑。

It may help to think through what your mySQL server actually has to do to perform your query.

可能有助于思考mySQL服务器实际执行查询时必须执行的操作。

 where hlcclientdetails.cldunifiedcrmid = ?

means it has to romp through your hlcclientdetails table looking for certain rows. So an index on cldunifiedcrmid will let it do that efficiently. You've mentioned that you have that index in place. Good.

意味着它必须通过你的hlcclientdetails表寻找某些行。因此，cldunifiedcrmid的索引将让它有效地做到这一点。你已经提到你已经有了这个索引。好。

Moving on,

继续，

   inner join hlcclientdetails 
              on  a.shdclientid = hlcclientdetails.cldregno 
             and  a.shdclientdcnid = hlcclientdetails.clddcnid

means it has to take the rows it found in your hlcclientdetails table and match them to your table1 a by looking at a.shdclientdcnid and a.shdclientid. So, your query can probably benefit from a compound index in table1 a for those two columns. You should try adding that index, and see whether performance gets better.

意味着它必须获取它在hlcclientdetails表中找到的行，并通过查看a.shdclientdcnid和a.shdclientid将它们与table1a匹配。因此，对于这两列，您的查询可能会受益于table1 a中的复合索引。您应该尝试添加该索引，并查看性能是否变得更好。

Finally,

最后，

inner join appointmentstatusmst st 
           on  st.entity_id = a.shdstatuslviid

means the server has to take the rows it found in table1 a and match them up to this third table. You mentioned in your comment that entity_id is the PK of that table. That will help.

表示服务器必须获取它在table1a中找到的行，并将它们匹配到第三个表。您在评论中提到entity_id是该表的PK。那会有所帮助。

You get the idea, I hope. Indexes help not only with WHERE clauses but with ON clauses.

我希望你能得到这个想法。索引不仅有助于WHERE子句，还有ON子句。

By the way, the full-text index mentioned in another answer won't help with this JOIN performance problem you are having.

顺便说一句，另一个答案中提到的全文索引对你遇到的这个JOIN性能问题没有帮助。

#1