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

[web_designer_iq]

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 :sick:
Query is:

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 DESC, jobs.added_time DESC LIMIT 0,10

When i remove this line the server health will be up! :eek:

`users_employers` AS users,

I uses this query in the index page to get the jobs.
You can see the Explain Select as an image in this topic.

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.

CREATE TABLE IF NOT EXISTS `users_employers` (
  `user_id` bigint(20) NOT NULL auto_increment,
  `user_name` varchar(50) default NULL,
  `user_pass` varchar(50) default NULL,
  `user_email` varchar(100) default NULL,
  `full_name` varchar(250) default NULL,
  `manager_name` varchar(120) default NULL,
  `about_me` mediumtext,
  `country_id` tinyint(3) default '0',
  `city_id` smallint(6) default '-1',
  `employed_number` smallint(6) NOT NULL,
  `mobile_no` varchar(20) default '0',
  `tel_no` varchar(20) default '0',
  `fax_no` varchar(20) default '0',
  `address` varchar(150) default '0',
  `user_photo` varchar(100) NOT NULL,
  `user_photo_is_accept` tinyint(1) NOT NULL default '0',
  `user_type` enum('EMPLOYER','COMPANY','OFFICE') NOT NULL default 'EMPLOYER',
  `user_expire` int(11) default NULL,
  `registration_date` int(11) default NULL,
  `how_many_login` int(11) NOT NULL default '0',
  `opt_hidden_mode` tinyint(1) NOT NULL default '0',
  `opt_enable_sms` tinyint(1) NOT NULL,
  `opt_email_notify` tinyint(3) default '1',
  `opt_view_employer_contact_info` tinyint(1) NOT NULL,
  `opt_user_style` varchar(100) default NULL,
  `opt_time_diff_h` enum('+','-') NOT NULL default '+',
  `opt_time_diff` tinyint(2) default NULL,
  `opt_time_format` varchar(50) default NULL,
  `opt_user_lang` varchar(100) default NULL,
  `sms_verify_code` smallint(5) default NULL,
  `sms_balance` mediumint(5) default NULL,
  `last_login` int(11) default NULL,
  `is_opened` tinyint(3) default '0',
  `last_ip` varchar(16) NOT NULL,
  `how_many_viewd` int(11) NOT NULL,
  `last_viewd` int(11) NOT NULL,
  `host` varchar(100) NOT 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=InnoDB  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.

CREATE TABLE IF NOT EXISTS `employer_jobs` (
  `id` int(11) NOT NULL auto_increment,
  `user_id` bigint(20) default NULL,
  `job_title` varchar(200) default NULL,
  `job_type` enum('FULL','PARTIAL','ALL') NOT NULL default 'ALL',
  `salary_from` bigint(20) NOT NULL,
  `salary_to` bigint(20) NOT NULL default '0',
  `currency` tinyint(3) NOT NULL,
  `job_cat_id` tinyint(10) default NULL,
  `country_id` tinyint(3) default '0',
  `city_id` smallint(6) default '-1',
  `about_job` mediumtext,
  `other_requirements` mediumtext,
  `comments` mediumtext,
  `age_from` tinyint(2) NOT NULL default '0',
  `age_to` tinyint(2) NOT NULL default '0',
  `degree_id` tinyint(3) default '0',
  `gender` enum('M','F','L') NOT NULL default 'L',
  `nationality_id` smallint(6) NOT NULL default '-1',
  `experience_years` tinyint(2) default NULL,
  `is_filter` tinyint(1) NOT NULL default '0',
  `view_email` tinyint(1) default '1',
  `view_mobile` tinyint(1) default '1',
  `view_tel` tinyint(1) default '1',
  `view_fax` tinyint(1) default '1',
  `added_time` int(11) default NULL,
  `is_accept` tinyint(3) default '1',
  `is_firm` tinyint(1) default '0',
  `host` varchar(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=InnoDB  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,

[tomhath]

How many rows does it return? It doesn't look like you've narrowed the search very much by picking jobs with host='localhost'.

[web_designer_iq]

Can you explain what do you mean please? the "host" field is important. Because there are 2 web site using the same database.

[tomhath]

This is how I would write the query, should give the same result but makes it easier to see how the JOINs go together and what the WHERE clause is actually selecting. As you can see it will return every combination of job/user/city where is_accept=1. That could easily be tens of thousands of rows depending on what's in your database.

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 jobs.user_id = users.user_id
INNER JOIN `cities` AS cities
   ON jobs.city_id = cities.city_id
WHERE
    jobs.is_accept = 1
    AND jobs.host = "localhost"
ORDER BY jobs

[web_designer_iq]

Many thanks
But the problem is not fixed !
When i execute the query without INNER JOIN:

Showing rows 0 - 29 (10,822 total, Query took 0.0400 sec)

When i execute the query with INNER JOIN:

Showing rows 0 - 29 (10,822 total, Query took 0.0404 sec)

I don't know what can do?
Is there any MySQL Performance like caching or something?
Thanks

[tomhath]

The problem is that your index isn't helping, you are selecting almost all rows in the table, then displaying them in reverse order of added_time. Try changing the index on added_time and is_firm to DESC (since that is how you are selecting the rows).

Maybe an index that covers all the columns like this (untested). You should also drop the other indexes (KEYs) unless you are selecting by them somewhere else:

create index new_jobs on employer_jobs 
(is_accept, host, is_firm DESC, added_time  DESC, city_id, user_id)

Ten thousand rows really shouldn't be a problem for MySQL though. Are you running on a machine without very much memory? You really should have at least a Gig. And preferably don't run anything else except MySQL on that machine (no web server, etc).

[web_designer_iq]

Great
I see the problem is fixed :-)
Thanks to every one
But i have another question:
How can i use inner join for this query:

	SELECT

			jobs.id AS job_id,
			jobs.job_title,
			jobs.job_type,
			jobs.job_cat_id,
			jobs.salary_from,
			jobs.user_id,
			jobs.salary_to,
			jobs.country_id,
			cnts.internet_tld,
			users.user_name,
			jobs.about_job,
			jobs.other_requirements,
			jobs.added_time ".$get_time_dif_h.($get_time_dif * 3600)." AS added_at

			FROM

			`"._TBLPREFIX.'employer_jobs` AS jobs,
			`'._TBLPREFIX.'users_employers` AS users,
			 '._TBLPREFIX.'countries AS cnts
WHERE
jobs.user_id = users.user_id 
&& jobs.country_id = cnts.country_id

etc...

[jansky]

if im the one doing this i will do this

 SELECT

            jobs.id AS job_id,
            jobs.job_title,
            jobs.job_type,
            jobs.job_cat_id,
            jobs.salary_from,
            jobs.user_id,
            jobs.salary_to,
            jobs.country_id,
            cnts.internet_tld,
            users.user_name,
            jobs.about_job,
            jobs.other_requirements,
            jobs.added_time ".$get_time_dif_h.($get_time_dif * 3600)." AS added_at

            FROM

            `"._TBLPREFIX.'employer_jobs` AS jobs // this will be my primary table 

   // inner join will be here 
   INNER JOIN `users_employers` AS users
       ON jobs.user_id = users.user_id 
INNER JOIN `cities` AS cities
       ON jobs.city_id = cities.city_id  

you are getting too much trouble in memory

FROM

            `"._TBLPREFIX.'employer_jobs` AS jobs,
            `'._TBLPREFIX.'users_employers` AS users,
             '._TBLPREFIX.'countries AS cnts

• you are querying three table at a time

[web_designer_iq]

Thank you very much
Yes, I fixed the problem by INNER. Now i get the "MySQL High performance" book is really great.
Thanks alot, 🙂