Question 20258 in Mysql Question posted in
The official MySQL documentation can be found here.

Query so slow on 3M records. Suggest any kind of optimization – Mysql

First of all, I’m sorry for the disorganized and confusing structure. It was decided by the previous team, and now I have to handle it all.

salesman_master 300 records

salesman_id salesman_name
1 Salesman 1
2 Salesman 2

client_master 9000 records

client_id client_name pricing_group
1 Client 1 1000
2 Client 2 1025

salesman_access Upto 4000 client/salesman

id client_id salesman_id
1 1 1
2 1 2
3 2 1
4 2 2

pricing_group 100 records

id pricing_group_name
1000 XYZ Prices
1025 ABC Prices

pricing_detail_master Upto 1000 sku/pricing_group

id pricing_group sku rate
1 1000 12033 100.00
2 1000 12038 200.00
1 1025 12033 300.00
2 1025 12038 400.00

prd_acc 3M records

id client_id sku
1 1 12033
2 1 12038
3 2 12033
4 2 12038

This is the query I’m trying to run. Now I have tried setting indexes on required columns for all of these tables but somehow query some times takes 30-40 seconds and some times it give error MySQL Server has gone away.

Please give any kind of structural or optimization advice, any kind of suggestion needed. Thanks

 SELECT * FROM 
   salesman_access sa 
 JOIN 
   client_master cm ON sa.client_id = cm.client_id 
 JOIN 
   pricing_detail_master pdm ON pdm.pricing_group = cm.pricing_group 
 JOIN
   prd_acc pa ON pa.client_id = cm.client_id 
 WHERE 
   sa.salesman_id = 202

fiddle link

Tags:

2

Answers


  1. 0

    Maybe you can do a subquery with the huge table and the tables which have the most direct way for the condition.
    Something like:

    SELECT * FROM 
  prd_acc pa
JOIN
  salesman_access sa ON pa.client_id = pa.client_id
WHERE 
   sa.salesman_id = 202

    This way you reduce the 3M to the expected 800-900 records, and with these you annex all the remaining columns

    SELECT * FROM (
  SELECT sa.* FROM 
    prd_acc pa
  JOIN
    salesman_access sa ON pa.client_id = pa.client_id
  WHERE 
    sa.salesman_id = 202 ) AS tps
JOIN
  client_master cm ON tps.client_id = cm.client_id 
JOIN 
  pricing_detail_master pdm ON pdm.pricing_group = cm.pricing_group

    Not sure if salesman_master was ommited on purpose, but you can annex it.

    Login or Signup to reply.
  2. 0

    this could be due to several things!

    1. How Much RAM or Disc Space du you have (free Spaces)? Is it Enoth?
    2. Do you have set a limit for the RAM-Usage?
    3. Look carefully on the Indexes which you
      set! Maybe you set a Index on a Unique Index Key? In this case The
      indexes "destroys" each other!

    More information would be helpful. They are also a Query-Analyzser for Mysql did you try it with them?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search