why the sign-in is slow?!

[jrahma]

Hi,

I have this page:

http://www.mybluefile.com/

it's too slow when I sign in.. there is no problem with the line because I am able to ping with good latency as you can see in this ping result:

http://www.jassimrahma.com/temp/ping_result.png

below is my stored procedure for the sign in and I have indexes applied.

where is the problem and how can investigate such case?

CREATE DEFINER=`root`@`%` PROCEDURE `sp_web_login_employee`(IN param_login_name varchar(255), IN param_login_password_salt varchar(255), IN param_login_password_hash varchar(255), IN param_ip_address varchar(255))
BEGIN
	DECLARE param_employee_id INT;
	DECLARE email_address VARCHAR(255);

-- IF EXISTS (SELECT user_id FROM users WHERE login_name = param_login_name AND login_password_hash = param_login_password_hash AND is_blocked = FALSE) THEN
IF EXISTS (SELECT employee_guid FROM employees WHERE login_name = param_login_name AND login_password_salt = param_login_password_salt AND login_password_hash = param_login_password_hash AND is_active = TRUE AND current_employee = TRUE) THEN
BEGIN
	DECLARE param_employee_guid VARCHAR(255);

	SET param_employee_id = (SELECT employee_id FROM employees WHERE login_name = param_login_name AND login_password_salt = param_login_password_salt AND login_password_hash = param_login_password_hash AND is_active = TRUE AND current_employee = TRUE);
	SET param_employee_guid = (SELECT employee_guid FROM employees WHERE login_name = param_login_name AND login_password_salt = param_login_password_salt AND login_password_hash = param_login_password_hash AND is_active = TRUE AND current_employee = TRUE);

	UPDATE employees SET last_login_date = UTC_TIMESTAMP(), last_login_ip = param_ip_address WHERE login_name = param_login_name AND login_password_salt = param_login_password_salt AND login_password_hash = param_login_password_hash AND is_active = TRUE AND current_employee = TRUE;
	-- CALL sp_add_event_log("USER", param_user_id, param_user_id, "User logged on successfully.");

	-- UPDATE employees SET last_login = NOW(), allow_delete = FALSE WHERE login_name = param_login_name AND login_password_salt = param_login_password_salt AND login_password_hash = param_login_password_hash AND is_active = TRUE;

	SET email_address = (SELECT contact_details FROM employee_contacts INNER JOIN contact_category ON contact_category.contact_category_id = employee_contacts.contact_category WHERE contact_category.contact_category_type = "EMAIL" AND employee_contacts.employee_id = param_employee_id AND employee_contacts.use_for_communication = TRUE);

	SELECT email_address, employees_table.employee_id, employees_table.employee_number, employees_table.employee_guid, employees_table.first_name, employees_table.second_name,
	employees_table.third_name, employees_table.fourth_name, employees_table.last_name, employees_table.official_name, employees_table.photo,
	managers_table.employee_id AS manager_id, managers_table.official_name AS manager_name, employee_job.contract_id, employee_contracts.contract_start,
	employee_contracts.contract_expiry, job_titles.job_title_name, departments.department_name, companies.company_name, locations.location_name, country.country_name
	FROM employees employees_table
	JOIN employee_contracts ON employees_table.employee_id = employee_contracts.employee_id
	JOIN employee_job ON employees_table.employee_id = employee_job.employee_id
	JOIN job_titles ON employee_job.job_title = job_titles.job_title_id
	JOIN companies ON employee_job.company_id = companies.company_id
	JOIN departments ON employee_job.department = departments.department_id
	JOIN locations ON employee_job.company_location = locations.location_id
	JOIN country ON locations.country_code = country.country_code_alpha2
	LEFT JOIN employees managers_table ON employee_job.reporting_to = managers_table.employee_id
	WHERE employees_table.login_name = param_login_name AND employees_table.login_password_salt = param_login_password_salt AND employees_table.login_password_hash = param_login_password_hash AND employees_table.is_active = TRUE AND employees_table.current_employee = TRUE AND employee_job.is_current = TRUE;
END;
END IF;

END

[NogDog]

I see 5 separate select queries and 2 updates (unless I missed some?). Do you really need to do all those separate queries each time someone logs in? (I.e., can any of the them be combined -- especially since most/all of them are against the same table?) Outside of that, EXPLAIN is your friend for finding out where the most time is being spent in your queries, which indexes are being used, where full table reads are happening in spite of indexes, and so forth.

[jrahma]

you mean all in one query? how? it's different queries, some are SET, UPDATE, etc

[Weedpacket]

As NogDog says, if you really need all that activity just to log someone in, then EXPLAIN will help identify where the bottlenecks lie. That nine-table join might be a good place to start.

As for whether all that activity is even needed

1. This apparent need for an employee_id and an employee_guid. Why both? If just one of them is enough to uniquely identify the employee, then the other is redundant.

2. But then param_employee_guid doesn't seem to be used anyway.

3. Having got a unique identifier or two for the employee, you could surely use it in the following UPDATE statement, instead of making the entire search again - otherwise it's not a very good unique identifier. Use it the way it is used when selecting the email address...

4. ...But not in that big join, where the whole search is performed again.

5. Can't selecting all that ancillary information, like the two-letter code for the country in which the company where the employee's job is located, wait until it's actually needed? It's not really part of the notion of "logging in".

And: moving to the appropriate forum.