optimization - optimize an mysql request : inner join very long

Question

I have a trouble with an sql request, which is short (0,07 sec) with a left join but very long when I need an inner join (8 sec)

How could I optimize my inner join request?

I have 2 table, customer(48 000 row) and customer2 (39 000 row). All the customers of customer 2 are included in customer. My request look like :

Select *
FROM customer
inner JOIN customer2 ON customer2.customer_id= customer.id    
WHERE customer.name LIKE '%' 
ORDER BY name LIMIT 300 

Any idea how to speed up this request? Thank you

Here are the Explain :

• first request (inner, very long)

<explain>
  <plan type="limit">
    <type>top</type>
    <rows>300</rows>
    <plan type="sort">
      <sort-key>
        <item>NOM</item>
      </sort-key>
      <plan type="materialize">
        <plan type="join">
          <join-method>nestedloops</join-method>
          <join-type>inner</join-type>
          <plan type="table">
            <name>CLIENSOC</name>
          </plan>
          <plan type="table">
            <name>CLIENTS</name>
            <filter>( ( C0.CLCLEUNIK = CL.CLCLEUNIK ) AND ( CL.NOM >=  '                     ') ) </filter>
          </plan>
        </plan>
      </plan>
    </plan>
  </plan>
</explain>

Second request, left (short) :

<?xml version="1.0" encoding="UTF-8"?>
<explain>
  <plan type="materialize">
    <plan type="limit">
      <type>top</type>
      <rows>300</rows>
      <plan type="join">
        <join-method>nestedloops</join-method>
        <join-type>left</join-type>
        <plan type="table">
          <name>CLIENTS</name>
          <filter>( CL.NOM >=  '') </filter>
          <index-scan key="NOM"/>
        </plan>
        <plan type="table">
          <name>CLIENSOC</name>
          <filter>( C0.CLCLEUNIK = CL.CLCLEUNIK ) </filter>
          <index-scan key="CLCLEUNIK"/>
        </plan>
      </plan>
    </plan>
  </plan>
</explain>

Answer 1

等待大神答复