Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: MySQL &.Net Performance

    I have one table with 50,000 records.
    In my website, i will let user to select this records by certain criteria, and display on match records. May be just 2000 records match, may be 30,000 records match, depands on the criteria search.
    The query is like below:

    SELECT a.*, Month(birth_date) as BirthMonth,
    year(now())- year(birth_date) as Age
    , concat(a.first_name, ' ', a.last_name, ' ', a.nick_name,' '
    , ifnull(b.job_desc, ' '), ' ', ifnull(c.area_desc, ' '), ' ', a.designation) as search, b.job_desc, c.area_desc
    FROM Member a
    LEFT JOIN JobIndustry b ON
    a.job_code = b.job_code
    LEFT JOIN Area c ON
    a.area = c.area_code
    LEFT JOIN company d ON a.co_code = d.co_code
    Where member_code not in (select member_code from MemberGrp where grp_code = "Favourite")
    and search like "%Account%Shelly";

    If user select to add all to group, i will insert all this match record to MemberGrp table.
    My Question is
    1. How to optimize my sql statement ?
    2. What method should i use to insert all this match records?
    Use "Insert into" and select again the record in then insert statement
    or
    loop the result from grid and insert one by one?

    My main consideration here in performance.

    looking for your advice.

    Thank in advance

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    What is the reason for a.* ?
    If you can migrate those LEFT JOINs into subselects it will reduce the work that the path execution planner does, as hopefully if your member_code is indexed (if not primary key) the where clause will reduce the number of JOIN/Subselects that are required to be done.
    Any particular reason why you are pulling out b.job_desc, c.area_desc in your select when they're ONLY used in the concat part of the query. Unless you're using this data somewhere else?
    Last edited by aschk; 04-05-07 at 09:20.

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    P.S. if you're intersted in performance use indexes...

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Also, please post the setup of your tables. And an expected resultset (if possible within legal boundaries).

  5. #5
    Join Date
    Jun 2004
    Posts
    57
    Quote Originally Posted by aschk
    What is the reason for a.* ?
    If you can migrate those LEFT JOINs into subselects it will reduce the work that the path execution planner does, as hopefully if your member_code is indexed (if not primary key) the where clause will reduce the number of JOIN/Subselects that are required to be done.
    Any particular reason why you are pulling out b.job_desc, c.area_desc in your select when they're ONLY used in the concat part of the query. Unless you're using this data somewhere else?
    Thank for you reply,
    1. subselect, did u mean using subquery instead of left join?
    2. member_code is indexed
    3. job_desc and area_desc not use only in concat part, this two field will be read and display to user also.
    4. use "select a.*" because this statement will be use in many place, which request different information.

Posting Permissions

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