mysql query retrieve too slow from 10,000 of data (Query Optimizing)

Question

10,000 of data in memberships, members and payments table. Retreiving the query is too slow, while searching a particular payment status in each members latest payment.

SELECT m.id AS member_id, m.full_name, m.unit, m.street, m.block, m.country,   m.postal_code, cat . * , cat.id AS cat_id, mem.membership_num, mem.id AS membership_id
FROM memberships mem
LEFT JOIN category cat ON mem.category_id = cat.id
LEFT JOIN members m ON mem.member_id = m.id
WHERE m.id >0
AND m.status = 'active'
AND (
  mem.category_id
  BETWEEN 1
  AND 11
)
AND (
  SELECT p1.payment_status_id
  FROM payments p1
  WHERE p1.category_id = cat.id
  AND p1.member_id = mem.member_id
  AND p1.payment_status_id = '1'
  LIMIT 1
  ) != ''
GROUP BY CONCAT( cat.id, '_', m.unit, '_', m.postal_code )
ORDER BY m.full_name ASC
LIMIT 0 , 25

EXPLAIN

Query Runs too slow 21.00sec to 99.00sec

Answer 1

---

ISSUE

The page is slowing down (3-5 mins instead of seconds) in mysql queries which has multiple joins and sub-queries to retrieve massive amount of data (~10,000) in tables.

---

SOLUTION - Used Index to the columns

Added indexes and done various search queries, It is retrieving better.

---

NOTES

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows. The larger the table, the more this costs. If the table has an index for the columns in question, MySQL can quickly determine the position to seek to in the middle of the data file without having to look at all the data. This is much faster than reading every row sequentially.

The best way to improve the performance of SELECT operations is to create indexes on one or more of the columns that are tested in the query. The index entries act like pointers to the table rows, allowing the query to quickly determine which rows match a condition in the WHERE clause, and retrieve the other column values for those rows. All MySQL data types can be indexed.

eg: ALTER TABLE `memberships` ADD INDEX ( `category_id` ) ;

DrawBack: Indexes are something extra that you can enable on your MySQL tables to increase performance,but they do have some downsides. When you create a new index MySQL builds a separate block of information that needs to be updated every time there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance.

Tutorials mysql.com, howtoforge.com, tizag.com

---

THANKS

@venca @Boris @Raja Amer Khan and all

Thanks for all helping me to solve the issue.

Answer 2

First make sure you have created indexes for all columns involved in JOINS and WHERE clause. Also, if you have made composite indexes then make sure they are in same order as in your query. Try following and see if it makes any difference:

SELECT m.id AS member_id, 
         m.full_name, 
         m.unit, 
         m.street, 
         m.block, 
         m.country,   
         m.postal_code, 
         cat . * , 
         cat.id AS cat_id, 
         mem.membership_num, 
         mem.id AS membership_id
FROM memberships mem
LEFT JOIN members m ON  m.id = mem.member_id
LEFT JOIN category cat ON cat.id = mem.category_id
INNER JOIN payments p1 ON p1.category_id = mem.category_id
                          AND p1.member_id = mem.member_id 
                          AND p1.payment_status_id = '1'
WHERE m.id > 0
AND mem.category_id BETWEEN 1 AND 11
AND m.status = 'active'
GROUP BY CONCAT( cat.id, '_', m.unit, '_', m.postal_code )
ORDER BY m.full_name ASC
LIMIT 0 , 25