MySQL Forums
Forum List  »  Optimizer & Parser

Help with query/tables optimization
Posted by: Oleg Ivanov
Date: April 14, 2012 05:20AM

I would need help with optimization of one query (tables used in that query), which currently takes 4 seconds to run. Although I use MySQL for a long time, I'm complete newbie if it takes to SQL performance.

Query it the following:
SELECT tsl.id
FROM (translation_selected_languages AS tsl)
LEFT JOIN translation_languages_translation AS tl1 ON tl1.parent_lang = tsl.language_from AND tl1.lang = "et"
LEFT JOIN translation_languages_translation AS tl2 ON tl2.parent_lang = tsl.language_to AND tl2.lang = "et"
LEFT JOIN users AS u ON tsl.author_id = u.id
LEFT JOIN user_profiles AS up ON up.original_user_id = u.id
WHERE `u`.`status` = 1
AND ( (up.lang = 'et' AND up.accepted = 1) OR (up.lang = 'en' AND up.accepted = 1) OR (up.lang = 'ru' AND up.accepted = 1) )
GROUP BY tl1.id, tl2.id
ORDER BY tl1.language asc, tl2.language asc

Tables are the following

CREATE TABLE IF NOT EXISTS `translation_selected_languages` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`language_from` int(3) NOT NULL,
`language_to` int(3) NOT NULL,
`author_id` int(11) NOT NULL,
`price` int(6) NOT NULL,
`price_eur` decimal(8,2) NOT NULL,
`internal_rating` tinyint(4) NOT NULL DEFAULT '0',
`accepted` tinyint(1) NOT NULL,
`date_time` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4639 ;

CREATE TABLE IF NOT EXISTS `translation_languages_translation` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`parent_lang` tinyint(2) NOT NULL,
`language` varchar(30) NOT NULL,
`lang` char(2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=258 ;

CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`agent_id` int(5) NOT NULL,
`referee_id` int(11) NOT NULL,
`referred_type` varchar(15) NOT NULL,
`client_status` tinyint(1) NOT NULL,
`first_name` varchar(128) NOT NULL,
`last_name` varchar(128) NOT NULL,
`personal_code` varchar(20) NOT NULL,
`payout_way` tinyint(1) NOT NULL DEFAULT '1',
`paypal` varchar(100) NOT NULL,
`bank_account` varchar(20) NOT NULL,
`user_address` varchar(100) NOT NULL,
`user_country` tinyint(3) NOT NULL,
`bic` varchar(15) NOT NULL,
`bank_name` varchar(50) NOT NULL,
`bank_address` varchar(100) NOT NULL,
`bank_country` tinyint(3) NOT NULL,
`user_name` varchar(128) NOT NULL,
`password` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`is_email_allowed` tinyint(1) NOT NULL DEFAULT '1',
`phone` varchar(100) NOT NULL,
`about` text NOT NULL,
`mother_language` int(3) NOT NULL,
`cv` varchar(50) NOT NULL,
`experience` text NOT NULL,
`education` text NOT NULL,
`cources` text NOT NULL,
`publications_total` tinyint(4) NOT NULL,
`publications_educational` tinyint(4) NOT NULL,
`publications_popular` tinyint(4) NOT NULL,
`publications_last_years` tinyint(4) NOT NULL,
`educational_work_contracts` tinyint(4) NOT NULL,
`educational_work_premiums` tinyint(4) NOT NULL,
`university_work_superwised` tinyint(4) NOT NULL,
`university_work_recensed` tinyint(4) NOT NULL,
`profession` varchar(100) NOT NULL,
`company_name` varchar(255) NOT NULL,
`company_address` varchar(255) NOT NULL,
`company_reg_code` varchar(15) NOT NULL,
`payout_target` tinyint(1) NOT NULL,
`balance` decimal(9,2) NOT NULL,
`balance_eur` decimal(8,2) NOT NULL,
`balance_freezed` decimal(9,2) NOT NULL,
`balance_freezed_eur` decimal(8,2) NOT NULL,
`rating` int(5) NOT NULL,
`internal_rating` tinyint(1) NOT NULL,
`user_type` tinyint(1) NOT NULL,
`loyalty_code` varchar(30) NOT NULL DEFAULT 'ordinary',
`price_copywriting` int(6) NOT NULL,
`price_rewriting` int(6) NOT NULL,
`preferred_lang` char(2) NOT NULL,
`status` tinyint(1) NOT NULL,
`comment` text NOT NULL,
`last_visit` datetime NOT NULL,
`country` char(2) NOT NULL DEFAULT 'et',
`registration_date` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3224 ;

CREATE TABLE IF NOT EXISTS `user_profiles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`original_user_id` int(11) NOT NULL,
`about` text NOT NULL,
`about_tmp` text NOT NULL,
`lang` char(2) NOT NULL DEFAULT 'et',
`accepted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1381 ;

Thanks in advance.

Options: ReplyQuote


Subject
Views
Written By
Posted
Help with query/tables optimization
2177
April 14, 2012 05:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.