Optimizing MySQL queries / database structure

Question

I have in my MySQL database these two tables:

CREATE TABLE IF NOT EXISTS `articles` (
  `id` bigint(20) NOT NULL,
  `url` varchar(255) collate utf8_bin NOT NULL,
  `img` varchar(255) collate utf8_bin NOT NULL,
  `name` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
  `url_key` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
  `type_code` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
  `likes_count` int(11) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `type` (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CREATE TABLE IF NOT EXISTS `articles_types` (
  `id` int(11) NOT NULL auto_increment,
  `code` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
  `url_key` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
  `name` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`code`),
  KEY `type` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

And I'm trying to run this query:

SELECT a.* FROM `articles` a INNER JOIN articles_types at ON at.`code`=a.type ORDER BY p.likes_count DESC LIMIT 1

Which returns 1 row, in 1sec.

The table articles consists of aproximately over 70k rows, articles_types only about 70 rows.

Is there any chance to optimize the structure (maybe engine?) of that tables, or optimize the query itself to make it faster?

Do you have indexes on these tables? If so let us know which columns are covered. — Dustin Laine
– Dustin Laine, Commented Sep 11, 2010 at 22:20
2Dustin: As you may see above, the tables consists of many indexes, now I've added a new index to articles.like_count, because the results are sorted by this column. But it didn't helped. — Radek Simko
– Radek Simko, Commented Sep 12, 2010 at 7:36
2Damien: Thanks for considiring about that :-) Trust me, if I'd get really answer, which would solve my problems, I'd accept that answer. — Radek Simko
– Radek Simko, Commented Sep 12, 2010 at 7:38

nos · Accepted Answer · 2010-09-11 22:20:57Z

4

You should examine the output of EXPLAIN on your query

It's likely the order by that gets you, so create an index on articles.likes_count

answered Sep 11, 2010 at 22:20

nos

231k60 gold badges436 silver badges516 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Radek Simko Over a year ago

You're absolutely right. The problem here is the ORDERing - but even after adding the index to articles.likes_count, it takes 1-2sec.Without sorting results (without ORDER BY) its very fast - e.g. 0.0004sec.

Radek Simko Over a year ago

It helped to use LEFT JOIN instead of INNER JOIN. Perhaps it may happend, that will appears an article with unknown category, so I'll have to check that in application.

Collectives™ on Stack Overflow

Optimizing MySQL queries / database structure

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related