Too slow sql query [closed]

pls help me about this problem. I have two table, t1 with 50 000 row, t2 10 row, and the query is too slow. Any easiest way speed up? Thanks.

$sql = $_db_->_sql_query("SELECT date FROM t1 WHERE date LIKE '{$year}-{$month}-%' GROUP BY date");
while($row = $_db_->_sql_fetch_array($sql)) {
$sql2 = $_db_->_sql_query("SELECT sf.date as date, so.name as name, so.id as id, COUNT(sf.id) as num FROM t1 sf LEFT JOIN t2 so ON sf.doc = so.id WHERE sf.date = '{$row["date"]}' GROUP BY sf.doc");
while($row2 = $_db_->_sql_fetch_array($sql2)) {
....................
}
}

Answer

Your query could be reduced to this (for example). Indexes on (date, doc) would help…

SELECT sf.date 
     , so.name 
     , so.id 
     , COUNT(sf.id) num 
  FROM t1 sf 
  LEFT 
  JOIN t2 so 
    ON sf.doc = so.id 
 WHERE sf.date >= '2020-02-01'
   AND sf.date < '2020-03-01'
 GROUP 
    BY sf.date 
     , so.name 
     , so.id

Advertisement

Answer