Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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                     |
+----+--------------------+-------+--------+---------------+---------+---------+------------------------+------+---------------------------------+
share|improve this question
    
Do you really need a LIKE search rather than an equality search? –  Mark Storey-Smith Aug 13 '12 at 13:11
    
@MarkStorey-Smith yes I need it, is the only way I know to get all the results related to some string if exists another let me know –  ReynierPM Aug 13 '12 at 13:39
    
Why do you think it is slow? Do you have an execution plan showing that? –  dezso Aug 13 '12 at 14:13
    
@dezso what's a execution plan? I'm not a MySQL expert can you give me some tips to test if is really slow or not? –  ReynierPM Aug 13 '12 at 14:45
    
@ReynierPM: Execution plan or EXPLAIN plan: Run EXPLAIN SELECT ..(your query).. \G from the command line and copy-paste the output in your question. –  ypercube Aug 13 '12 at 16:31
show 4 more comments

1 Answer 1

I don't know for sure in mysql but I see a bunch of things that casue performance problems in SQL server. First, correlated subquereis are often performance killers as teh run row by agonizing row insted of in sets.

Next, do not use a wildcard as the first character in a where clasue as it prevents index usage. Either your table is incorrectly designed that you need to do this (such as when you store a comma delimited list) or you need to start using some type of full text search instead. Frankly there is no reason why users can't put in at least the first charcters of the email or username, so the wildcard is probably not necessary at all. Look at your requirements and be sure you are not goldplating and harming the system inthe process.

Next, no production code should ever use select * espcially when there is a join. You have repeated the join field twice by doing this which is wasteful of server reources and network resources. And if you are not actually using all of the other columns that is wasteful too. Further, I know in SQl server there is a performance hit while it looks up the column names and this may be true for myssql. At any rate it is a SQl antipattern just like using implicit joins is a SQl antipattern. It is also dangerous for maintenance as things which are added to the tables should not automactially be added to the query. You could end up showing things in the wrong place (if some person rearranges the columns) or returing data that you do not use or even showing some fields that you don't want the user to see. This is a very poor practice.

share|improve this answer
    
totally agree with you and all the explanation you leave here wow is amazing. Now I'm using wildcard * because I'm testing but of course this is not my finally query. I'll use something like u.email, u.username, p.first_name, p.last_name and so on. I think in use JOIN instead this but I didn't know how to build the query for that reason I ask for some help could you give me some and point me in the right direction? How you override this query using JOIN? –  ReynierPM Aug 14 '12 at 0:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.