Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2015
    Posts
    2

    Unanswered: Idea to Optimize MySQL Table Structure is Needed

    Hi, I have a query that running without issue when the selected data is low (less than 1 sec). but when i selecting large data with the same query but different value of the subscriber code, it will become very slow (estimate 30 sec). all necessary indexes are added to the table. can anyone please advice me how can i optimize this query / table structure to enhance it's performance?

    Thanks in advance.

    here is the query:
    select count(*) as cnt from tb_default a, tb_link b, tb_master c
    where a.EntityID = b.EntityID AND a.DefaultID = b.DefaultID AND a.SubscriberCode = 'CAA' AND b.EntityID = c.EntityID
    AND a.DeleteDate = '0000-00-00' and EntityType='C';


    and here is the result after explained
    Click image for larger version. 

Name:	explain.png 
Views:	3 
Size:	8.8 KB 
ID:	16488

  2. #2
    Join Date
    Aug 2015
    Posts
    1
    Quote Originally Posted by waichong


    select count(*) as cnt from tb_default a, tb_link b, tb_master c
    where a.EntityID = b.EntityID AND a.DefaultID = b.DefaultID AND a.SubscriberCode = 'CAA' AND b.EntityID = c.EntityID
    AND a.DeleteDate = '0000-00-00' and EntityType='C';
    hi,

    gereral rules : don't use count(*) but use count(attribute) . This is better .

    you have added the index on this attributes : a.EntityID ; b.EntityID ; a.DefaultID ; b.DefaultID ; a.SubscriberCode ; c.EntityID ; a.DeleteDate ; EntityType ??

  3. #3
    Join Date
    Jul 2015
    Posts
    2
    Quote Originally Posted by bomberdini11 View Post
    hi,

    gereral rules : don't use count(*) but use count(attribute) . This is better .

    you have added the index on this attributes : a.EntityID ; b.EntityID ; a.DefaultID ; b.DefaultID ; a.SubscriberCode ; c.EntityID ; a.DeleteDate ; EntityType ??
    Thank you for your reply, bomberdini11.

    Actually, i tried with count(a.EntityID) and it doesn't really help much. and all the attributes that you mentioned are indexed.

    Any idea i can speedup the process of the query?

    Thanks in advance!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •