I've three tables: default_users, default_profiles, default_friend. This are the SQL for each one:
--
-- Table structure for table `default_users`
--
CREATE TABLE IF NOT EXISTS `default_users` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(60) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`password` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`salt` varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`group_id` int(11) DEFAULT NULL,
`ip_address` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
`active` int(1) DEFAULT NULL,
`activation_code` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_on` int(11) NOT NULL,
`last_login` int(11) NOT NULL,
`username` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`forgotten_password_code` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
`remember_code` varchar(40) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16 ;
--
-- Table structure for table `default_profiles`
--
CREATE TABLE IF NOT EXISTS `default_profiles` (
`id` int(9) NOT NULL AUTO_INCREMENT,
`created` datetime DEFAULT NULL,
`updated` datetime DEFAULT NULL,
`created_by` int(11) DEFAULT NULL,
`ordering_count` int(11) DEFAULT NULL,
`user_id` int(11) unsigned NOT NULL,
`display_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`company` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`address_line1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`postcode` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`website` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`updated_on` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=16 ;
--
-- Table structure for table `default_friend`
--
CREATE TABLE IF NOT EXISTS `default_friend` (
`friend_id` mediumint(8) NOT NULL,
`user_id` mediumint(8) unsigned NOT NULL,
`is_subscriber` tinyint(1) NOT NULL DEFAULT '1',
`privacy` tinyint(3) DEFAULT '0',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`friend_list_id` smallint(5) DEFAULT NULL,
`approved` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
I'm building a search engine for my site so I need to find all users where username or email like some string but also needs to get for each of the results in those users which are my friends meaning where the result default_users.id is in friend.user_id or friend.friend_id because I can invite people but others can invite me too. I build this query:
SELECT
u.*
, p.*
, (
SELECT f.approved
FROM default_friend f
WHERE (
f.user_id = u.id
AND f.friend_id = 1
) OR (
f.friend_id = u.id
AND f.user_id = 1
)
LIMIT 1
) AS approved
FROM
default_users u
, default_profiles p
WHERE
(
u.email LIKE '%some_string%'
OR u.username LIKE '%some_string%'
)
AND u.id != 1
AND p.user_id = u.id
GROUP BY u.id
Any help on optimization of this? I think is a bit slowly and I'll have around 15 000 records in each table.
EXPLAIN
plan:
+----+--------------------+-------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
| 1 | PRIMARY | p | ALL | user_id | NULL | NULL | NULL | 18 | Using temporary; Using filesort |
| 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 2 | comvivem_db1.p.user_id | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | f | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+--------------------+-------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
Best Answer
I used
JOIN
andLEFT JOIN
to modified your query: