Why this query is so slow ?

web_designer_iq

Hi
I have 2 table in MySQL database, But the following query is so slow! I don't know what's wrong!

$MySQL->do_query('SELECT
		jobs.id AS job_id,
		jobs.job_title,
		jobs.added_time '.$get_time_dif_h.($get_time_dif * 3600).' AS added_time,
		jobs.about_job,
		users.full_name,
		jobs.user_id,
		jobs.host,
		jobs.is_firm,
		cities.city_name,
		jobs.country_id


FROM
	`'._TBLPREFIX.'employer_jobs` AS jobs INNER JOIN  `'._TBLPREFIX.'users_employers` AS users ON jobs.user_id = users.user_id,
	`'._TBLPREFIX.'cities` AS cities

WHERE
	jobs.is_accept = 1
	AND jobs.host = "'.$getHostName.'"
	AND jobs.city_id = cities.city_id
	'.$setViewOnly2.'
	ORDER BY jobs.is_firm DESC, jobs.added_time DESC LIMIT '.$Paging->sql());

I have 250.000 record in "users" table and "100.000" record in jobs!!
Tables structures:

CREATE TABLE `jobsonsms_employer_jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `job_title` varchar(100) DEFAULT NULL,
  `job_type` enum('FULL','PARTIAL','ALL') DEFAULT NULL,
  `salary_from` bigint(20) DEFAULT NULL,
  `salary_to` bigint(20) DEFAULT NULL,
  `currency` tinyint(3) DEFAULT NULL,
  `job_cat_id` int(11) DEFAULT NULL,
  `country_id` smallint(6) DEFAULT NULL,
  `city_id` smallint(6) DEFAULT NULL,
  `about_job` text,
  `other_requirements` text,
  `comments` text,
  `age_from` tinyint(2) DEFAULT NULL,
  `age_to` tinyint(2) DEFAULT NULL,
  `degree_id` tinyint(1) DEFAULT NULL,
  `gender` enum('M','F','L') DEFAULT NULL,
  `nationality_id` smallint(6) DEFAULT NULL,
  `experience_years` tinyint(2) DEFAULT NULL,
  `is_filter` tinyint(1) DEFAULT NULL,
  `view_email` tinyint(1) DEFAULT NULL,
  `view_mobile` tinyint(1) DEFAULT NULL,
  `view_tel` tinyint(1) DEFAULT NULL,
  `view_fax` tinyint(1) DEFAULT NULL,
  `added_time` int(11) DEFAULT NULL,
  `is_accept` tinyint(1) DEFAULT NULL,
  `is_firm` tinyint(1) DEFAULT NULL,
  `host` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=cp1256;

CREATE TABLE `jobsonsms_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(50) DEFAULT NULL,
  `manager_name` varchar(120) DEFAULT NULL,
  `about_me` text,
  `country_id` smallint(6) DEFAULT NULL,
  `city_id` smallint(6) DEFAULT NULL,
  `employed_number` smallint(6) DEFAULT NULL,
  `mobile_no` bigint(15) DEFAULT NULL,
  `tel_no` bigint(20) DEFAULT NULL,
  `fax_no` bigint(20) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `user_photo` varchar(100) DEFAULT NULL,
  `user_photo_is_accept` tinyint(1) DEFAULT NULL,
  `user_type` enum('EMPLOYER','COMPANY','OFFICE') DEFAULT NULL,
  `user_expire` int(11) NOT NULL DEFAULT '0',
  `registration_date` int(11) NOT NULL DEFAULT '0',
  `how_many_login` int(11) DEFAULT NULL,
  `opt_hidden_mode` tinyint(1) DEFAULT '0',
  `opt_enable_sms` tinyint(1) DEFAULT NULL,
  `opt_email_notify` tinyint(1) DEFAULT NULL,
  `opt_view_employer_contact_info` tinyint(1) DEFAULT NULL,
  `opt_user_style` varchar(100) DEFAULT NULL,
  `opt_time_diff_h` enum('+','-') DEFAULT NULL,
  `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) NOT NULL DEFAULT '0',
  `last_login` int(11) DEFAULT NULL,
  `is_opened` tinyint(1) DEFAULT NULL,
  `last_ip` varchar(16) DEFAULT NULL,
  `how_many_viewd` int(11) DEFAULT '0',
  `last_viewd` int(11) NOT NULL DEFAULT '0',
  `host` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`),
  UNIQUE KEY `user_email` (`user_email`),
  UNIQUE KEY `mobile_no` (`mobile_no`),
  KEY `country_id` (`country_id`),
  KEY `city_id` (`city_id`),
  KEY `tel_no` (`tel_no`),
  KEY `fax_no` (`fax_no`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=cp1256;

Please help how can i fast the query ?
Thanks

NogDog

I didn't look at all that stuff in detail, but the first thing would be to confirm that each column that is used in the WHERE condition or as a JOIN column has an index assigned to it.

Other than that, you could use EXPLAIN to help get some performance info on the query.

Sxooter

web.designer.iq;10905261 wrote:

Hi
I have 2 table in MySQL database, But the following query is so slow! I don't know what's wrong!

	FROM
		`'._TBLPREFIX.'employer_jobs` AS jobs INNER JOIN  `'._TBLPREFIX.'users_employers` AS users ON jobs.user_id = users.user_id,
		`'._TBLPREFIX.'cities` AS cities

WHERE
	jobs.is_accept = 1
	AND jobs.host = "'.$getHostName.'"
	AND jobs.city_id = cities.city_id
	'.$setViewOnly2.'
	ORDER BY jobs.is_firm DESC, jobs.added_time DESC LIMIT '.$Paging->sql());

[/code]

OK, part of the problem may be your strange join syntax. you should either do all joins in the where clause, or all joins with on clauses. Doing both forces the query planner to follow a specific order, and it can't make the join more efficient, even if it wants to. This is what the SQL spec says has to happen btw, so on earlier mysql it would run them any which way it wanted, but somewhere along the line in early 5.0 they fixed the bug and now the join order is constrained. move the AND jobs.city_id = cities.city_id bit up into the from part of your query and try again.