title: How to optimize a MySQL query joining three tables
body: <p>I have a query join 3 tables for sales with about 15000 rows. The query takes about 22 seconds to respond, this is too big for me. How can I optimize this query? I use Laravel and use the filter from the form.</p>
  <p>Here is my query :</p>
  <pre><code>$data = DB::table('crm_donhang as dh')
      -&gt;select('dh.id as id', 'madh', 'dh.created_at as ngaybg', 'ngayxn', 'dh.trangthai', 'dh.createdby as nhanvienbh', 'tongtien', 'dh.updated_at', 'dh.updatedby',
                  'ngayyc', 'tenkhachhang', 'tenloai', 'loaidiachi', 'dh.danhgia', 'dh.nguyennhan')
      -&gt;join(DB::raw('(SELECT id_donhang, masp FROM crm_donhang_ct as ct INNER JOIN crm_sanpham  as sp ON sp.id=ct.id_sanpham) as ctdh'), 'ctdh.id_donhang', '=', 'dh.id')
      -&gt;join('crm_khachhang as kh', 'kh.id', '=', 'dh.id_khachhang')
      -&gt;join('crm_cfg_loaidh as ldh', 'ldh.id', '=', 'dh.id_loaidh')
      -&gt;join('crm_cfg_dcgiaohang as dc', 'dc.id', '=', 'dh.id_dcgiaohang')
      -&gt;get();
  </code></pre>
  <p>I have an old database, I copied the crm_sanpham table into it after trying the query for 0.5 seconds, while in the new database it took 4.7 seconds. What happened? (While the 2 tables are identical, the installed database is the same).</p>
  <pre><code>select `id`, `masp`, `ketcau`, `mota`, `donvitinh`, `dongia`, `trangthai`, `created_at`, `updated_at`, `createdby`, `updatedby` 
  from `crm_sanpham` where `masp` != &quot;TANDUNG&quot; order by `masp` desc limit 10 offset 0
  </code></pre>