SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Why my query is very very slow? Anyone can help please

    Hello
    I'm so tired and i don't know what can i do. I did everything to improve this query but no way!!!
    This query will "stopping" my server!! The load will be up to 250% for MySQL!!
    The server will be "DOWN"!! I will be crazy too

    Query is:
    PHP Code:
    SELECT
            jobs
    .id AS job_id,
            
    jobs.job_title,
            
    jobs.added_time +10800 AS added_time,
            
    jobs.about_job,
            
    users.full_name,
            
    jobs.user_id,
            
    jobs.host,
            
    jobs.is_firm,
            
    cities.city_name,
            
    jobs.country_id


        FROM
            
    `employer_jobs` AS jobs,
            `
    users_employers` AS users,
            `
    cities` AS cities

        WHERE
            jobs
    .is_accept 1
            
    AND jobs.host "localhost"
            
    AND jobs.city_id cities.city_id
            
    AND jobs.user_id users.user_id
            ORDER BY jobs
    .is_firm DESCjobs.added_time DESC LIMIT 0,10 
    When i remove this line the server health will be up!
    PHP Code:
    `users_employers` AS users
    I uses this query in the index page to get the jobs.


    Employers Accounts Tables Strucutes
    Table Information:
    Total Records: 21,211
    Total Space: 8.1 MiB
    Purpose: I use this table to get the "full_name" field.
    PHP Code:
    CREATE TABLE IF NOT EXISTS `users_employers` (
      `
    user_idbigint(20NOT NULL auto_increment,
      `
    user_namevarchar(50) default NULL,
      `
    user_passvarchar(50) default NULL,
      `
    user_emailvarchar(100) default NULL,
      `
    full_namevarchar(250) default NULL,
      `
    manager_namevarchar(120) default NULL,
      `
    about_memediumtext,
      `
    country_idtinyint(3) default '0',
      `
    city_idsmallint(6) default '-1',
      `
    employed_numbersmallint(6NOT NULL,
      `
    mobile_novarchar(20) default '0',
      `
    tel_novarchar(20) default '0',
      `
    fax_novarchar(20) default '0',
      `
    addressvarchar(150) default '0',
      `
    user_photovarchar(100NOT NULL,
      `
    user_photo_is_accepttinyint(1NOT NULL default '0',
      `
    user_typeenum('EMPLOYER','COMPANY','OFFICE'NOT NULL default 'EMPLOYER',
      `
    user_expireint(11) default NULL,
      `
    registration_dateint(11) default NULL,
      `
    how_many_loginint(11NOT NULL default '0',
      `
    opt_hidden_modetinyint(1NOT NULL default '0',
      `
    opt_enable_smstinyint(1NOT NULL,
      `
    opt_email_notifytinyint(3) default '1',
      `
    opt_view_employer_contact_infotinyint(1NOT NULL,
      `
    opt_user_stylevarchar(100) default NULL,
      `
    opt_time_diff_henum('+','-'NOT NULL default '+',
      `
    opt_time_difftinyint(2) default NULL,
      `
    opt_time_formatvarchar(50) default NULL,
      `
    opt_user_langvarchar(100) default NULL,
      `
    sms_verify_codesmallint(5) default NULL,
      `
    sms_balancemediumint(5) default NULL,
      `
    last_loginint(11) default NULL,
      `
    is_openedtinyint(3) default '0',
      `
    last_ipvarchar(16NOT NULL,
      `
    how_many_viewdint(11NOT NULL,
      `
    last_viewdint(11NOT NULL,
      `
    hostvarchar(100NOT NULL,
      
    PRIMARY KEY  (`user_id`),
      
    KEY `user_name` (`user_name`),
      
    KEY `user_email` (`user_email`),
      
    KEY `country_id` (`country_id`),
      
    KEY `city_id` (`city_id`),
      
    KEY `user_photo_is_accept` (`user_photo_is_accept`),
      
    KEY `user_type` (`user_type`),
      
    KEY `opt_enable_sms` (`opt_enable_sms`),
      
    KEY `opt_email_notify` (`opt_email_notify`),
      
    KEY `opt_view_employer_contact_info` (`opt_view_employer_contact_info`),
      
    KEY `is_opened` (`is_opened`),
      
    KEY `host` (`host`)
    ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23248 
    --------------------------------------------------------------------------
    Employers Accounts Tables Strucutes
    Table Information:
    Total Records: 10,839
    Total Space: 4.1 MiB
    Purpose: I use this table to get the "employers" jobs.

    PHP Code:
    CREATE TABLE IF NOT EXISTS `employer_jobs` (
      `
    idint(11NOT NULL auto_increment,
      `
    user_idbigint(20) default NULL,
      `
    job_titlevarchar(200) default NULL,
      `
    job_typeenum('FULL','PARTIAL','ALL'NOT NULL default 'ALL',
      `
    salary_frombigint(20NOT NULL,
      `
    salary_tobigint(20NOT NULL default '0',
      `
    currencytinyint(3NOT NULL,
      `
    job_cat_idtinyint(10) default NULL,
      `
    country_idtinyint(3) default '0',
      `
    city_idsmallint(6) default '-1',
      `
    about_jobmediumtext,
      `
    other_requirementsmediumtext,
      `
    commentsmediumtext,
      `
    age_fromtinyint(2NOT NULL default '0',
      `
    age_totinyint(2NOT NULL default '0',
      `
    degree_idtinyint(3) default '0',
      `
    genderenum('M','F','L'NOT NULL default 'L',
      `
    nationality_idsmallint(6NOT NULL default '-1',
      `
    experience_yearstinyint(2) default NULL,
      `
    is_filtertinyint(1NOT NULL default '0',
      `
    view_emailtinyint(1) default '1',
      `
    view_mobiletinyint(1) default '1',
      `
    view_teltinyint(1) default '1',
      `
    view_faxtinyint(1) default '1',
      `
    added_timeint(11) default NULL,
      `
    is_accepttinyint(3) default '1',
      `
    is_firmtinyint(1) default '0',
      `
    hostvarchar(255) default NULL,
      
    PRIMARY KEY  (`id`),
      
    KEY `user_id` (`user_id`),
      
    KEY `country_id` (`country_id`),
      
    KEY `city_id` (`city_id`),
      
    KEY `nationality_id` (`nationality_id`),
      
    KEY `job_type` (`job_type`),
      
    KEY `job_cat_id` (`job_cat_id`),
      
    KEY `gender` (`gender`),
      
    KEY `experience_years` (`experience_years`),
      
    KEY `view_email` (`view_email`),
      
    KEY `view_mobile` (`view_mobile`),
      
    KEY `view_tel` (`view_tel`),
      
    KEY `view_fax` (`view_fax`),
      
    KEY `is_accept` (`is_accept`),
      
    KEY `added_time` (`added_time`),
      
    KEY `is_firm` (`is_firm`),
      
    KEY `host` (`host`),
      
    KEY `added_time_2` (`added_time`)
    ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21673 
    My Solution for this
    I just have one solution is: Storing the "full_name" field in the "employer_jobs" But what if the user changed the full name? I need to update the table again. I see is not a good solution for the problem.


    I WILL BE SO HAPPY IF YOU CAN HELP ME TO IMPROVE THIS "IMPOSSIBLE QUERY"

    Thanks,
    Last edited by web.designer.iq; Mar 21, 2009 at 09:31.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    please do an EXPLAIN for the query

    also, was this supposed to be equal to something? --
    Code:
    AND jobs.country_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry for that. It just was an old statement. I removed it
    Up

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    did you look at the EXPLAIN?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    I attached 2 picture for the "EXPLAIN".
    Attached Images Attached Images

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    thank you, that information may be useful

    the EXPLAIN is actually something that you have to run

    take your query, open it as SQL, type EXPLAIN in front of the SELECT keyword, and run it

    copy the output back here (formatted if possible in rows, there should be 3 or 4 rows)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Thanks for your fast response sir :-)
    I attached the "EXPLAIN SELECT ..." as an image.
    Thanks
    Attached Images Attached Images

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that looks fine to me

    see the first line? it says it's using the "host" KEY to find the rows in the jobs table

    the other two rows are fully optimized, as they use the primary keys to join those tables

    perhaps the slowness is due to the filesort (Extra information in line 1) which is required by the ORDER BY -- you have to sort all the localhost active jobs, in order to find the top 10

    you might also be able to improve performance by tuning your mysql server (buffers, etc.) but i don't know how to do that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are a great Man, Thanks for your help
    Did you mean everything in my SQL statements is ok?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by web.designer.iq View Post
    Did you mean everything in my SQL statements is ok?
    mostly

    i would use INNER JOIN syntax, like this --
    Code:
    SELECT jobs.id AS job_id
         , jobs.job_title
         , jobs.added_time +10800 AS added_time
         , jobs.about_job
         , users.full_name
         , jobs.user_id
         , jobs.host
         , jobs.is_firm
         , cities.city_name
         , jobs.country_id
      FROM employer_jobs AS jobs
    INNER
      JOIN users_employers AS users
        ON users.user_id = jobs.user_id 
    INNER
      JOIN cities AS cities
        ON cities.city_id = jobs.city_id
     WHERE jobs.is_accept = 1
       AND jobs.host = "localhost"
    ORDER 
        BY jobs.is_firm DESC
         , jobs.added_time DESC 
    LIMIT 10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    unfortunately, The problem is not fixed by "INNER"

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by web.designer.iq View Post
    unfortunately, The problem is not fixed by "INNER"
    i did not say that it would be, i merely said that i would use JOIN syntax instead of the way you coded your query, but the performance would be the same, caused by reasons other than the SQL itself, more to do with index optimization and maybe server tuning
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank to every one
    I see the problem is fiexd :-)
    I used INNER JOIN to every query i have in the site. And it's working now. :-)
    Thanks again :-)

  14. #14
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much sit

  15. #15
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But please can i ask you why the "WHERE" is take a long time to load my pages? And why "INNER JOIN" is take a little time? I see the syntax is same!

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    if the query is still too slow, you will need to re-analyse your indexes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Enthusiast
    Join Date
    Nov 2006
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi again
    Let me to write something.
    I'm using this encoding "latin1_swedish_ci" to store the "cp1256" encoding in the database.
    Is this encoding will hung up my database because i use it to save another character in different encoding type.
    When i convert them to the "cp_1256" what will be up?
    Thanks


Bookmarks

Posting Permissions

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