Mysql query too slow out of 10,000 data (query optimization)
10,000 data in members, members and payment table. Retrieving the request is too slow when looking for a specific payment status in the latter case of each member.
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
Execution of requests is too slow from 21.00 to 99.00 sec.
source to share
QUESTION
The page slows down (3-5 minutes instead of seconds) on mysql queries which has multiple joins and subqueries to retrieve large amounts of data (~ 10,000) in tables.
SOLUTION - Index used for columns
Indexes have been added and various searches are performed. He recovers better.
NOTES
Indexes are used to quickly find rows with specific column values. Without an index, MySQL has to start at the first row and then read the entire table to find matching rows. The larger the table, the more it costs. If the table has an index on the columns in question, MySQL can quickly determine the position to look for in the middle of the data file, without having to go through all the data. This is much faster than reading each line sequentially.
The best way to improve the performance of SELECT operations is to create indexes on one or more columns that have been tested in the query. Index records act as pointers to table rows, allowing you to quickly determine which rows match a condition in the WHERE clause and retrieve 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 improve performance, but they have some disadvantages. When you create a new index, MySQL builds a separate block of information that needs to be updated every time a change is made to the table. This means that if you are constantly updating, inserting, and deleting records in your table, it can adversely affect performance.
Tutorials mysql.com , howtoforge.com , tizag.com
THANK
@venca @Boris @Raja Amer Khan and all
Thanks for helping me solve this problem.
source to share
First, make sure you have created indexes on all the columns participating in the JOINS and WHERE clauses. Also, if you've created composite indexes, make sure they are in the same order as in your query. Try to follow and see, it doesn't matter:
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
source to share