Query taking too long to respond

I have php list of items.

I am fetching a array from my sql database.

It was working ok but recently records are increasing and suddenly it is not showing records in list.

Suggest!

I have tried inner join which have less record for that is was working ok

query is:

  SELECT farmer.*, state.state_name, videos.video_url, employee.name AS nm 
      FROM farmer 
      LEFT JOIN state ON state.id = farmer.state 
      LEFT JOIN videos ON videos.farmer_id = farmer.id 
      LEFT JOIN employee ON farmer.employee_id = employee.id 
      WHERE farmer.is_deleted = 0 
      ORDER BY farmer.id DESC;

Answer

To debug how this query is being executed by MySQL use EXPLAIN

• Optimizing Queries with EXPLAIN
• EXPLAIN Output Format: This will help to you understand which columns ref are being used in Joins.

A simple solution to such problems would be the proper use of indexes.

• Based on the output of EXPLAIN, decide which column ref are being used while performing the join.
• Create an index on those columns, either separate indexes or composite one based on their behavior.

CREATE INDEX index_name ON table_name (col_name);  -- for composite key you can mention multiple columns.

How it helps:

• Without an index, MySQL scans the complete table for each record of joining table to match a particular record.
• The index will help MySQL to directly access the desired record itself, which can drastically improve performance.

Reference:

• Optimize Queries for Speed & Performance – a quick guide with explain output and their explanation.

• For further query performance improvement, checkout MySQL Performance Optimization section in the official documentation.