1

I have this comments table with over 4 million rows:

CREATE TABLE `comments` 
(
     `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
     `gid` int(11) unsigned NOT NULL DEFAULT '0',
     `userid` int(6) unsigned DEFAULT NULL,
     `date` int(11) unsigned DEFAULT NULL,
     `comment` text NOT NULL,
     `status` enum('on','alert') NOT NULL DEFAULT 'on',
     PRIMARY KEY (`id`),
     KEY `gid_2` (`gid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

now I'm thinking about extracting the text-field to decrease the 400 MB and increase performance. Like this:

CREATE TABLE commentstext 
(
    id int(11) unsigned NOT NULL AUTO_INCREMENT,
    `comment` text NOT NULL,
    PRIMARY KEY (id)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

but I'm not sure if this will perform better this way. I need to test this cases by using different queries (also). My results so far differ a lot. Between 0.001* - 3.321 sec. I'm not able to check this by querying in phpmyadmin.

Is there a better and easy way or tool to compare queries performance?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tom
  • 303
  • 2
  • 5
  • 15
  • can you maybe explain a bit more about the queries that you think you need optimizing? show the code snippets maybe? – Pavel Veller Apr 18 '12 at 18:31
  • http://stackoverflow.com/questions/10215882/mysql-select-from-t1-t2-or-from-t1-join-t2-on – Tom Apr 19 '12 at 15:05
  • the queries are not as much important in this thread. I need to test different database structures. I've read about Super Smack, SysBench, mybench,... but I'm still looking for a less complicate solution. – Tom Apr 20 '12 at 12:50

1 Answers1

0

That's what I was looking for:

SELECT BENCHMARK(1000000000, (
SELECT
comments.comment
FROM
comments
WHERE
`gid`=303410
LIMIT 1
));

(result 34.1612 sec.) (result 32.2737 sec.)

SELECT BENCHMARK(1000000000, (
SELECT
commentstext.comment
FROM
commentsindex,
commentstext
WHERE
`gid`=303410
AND commentsindex.`id` = commentstext.`id`
LIMIT 1
));

(result 34.1237 sec.) (result 34.2914 sec.)

SELECT BENCHMARK(1000000000, (
SELECT
commentstext.comment
FROM
commentsindex
INNER JOIN
commentstext
ON commentstext.`id` = commentsindex.`id`
WHERE
`gid`=303410
LIMIT 1
));

(result 32.8471 sec.) (result 34.7079 sec.)

... but now I'm really wondering that it doesn't matter, which table design is in use. confused

Tom
  • 303
  • 2
  • 5
  • 15