Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Why is GROUP BY on FULLTEXT INDEX using temporary?

I'm using this table (MySQL / Engine: MyISAM) :

CREATE TABLE `activities` (
  `id_activity` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_doc` int(10) unsigned NOT NULL DEFAULT '0',
  `node_id` tinytext NOT NULL,
  `title` tinytext NOT NULL,
  `name` tinytext NOT NULL,
  `keywords` tinytext NOT NULL,
  `page_type` tinytext NOT NULL,
  `page_screen_id` tinytext NOT NULL,
  `page_screen_question` tinytext NOT NULL,
  PRIMARY KEY (`id_activity`),
  KEY `name` (`name`(255)),
  FULLTEXT KEY `node_id` (`node_id`,`title`,`name`,`keywords`,`page_type`,`page_screen_id`,`page_screen_question`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

(There are about 100000 rows)

Here is my query:

EXPLAIN SELECT 1
FROM `activities`
GROUP BY `node_id`, `title`, `name`, `keywords`, `page_type`, `page_screen_id`, `page_screen_question`;
  • id: 1
  • select_type: SIMPLE
  • table: activities
  • type: ALL
  • possible_keys: NULL
  • key: NULL
  • key_len: NULL
  • ref: NULL
  • rows: 613011
  • Extra: Using temporary; Using filesort

I do not understand why my query uses temporary... and I don't know how to avoid this... Thanks

like image 568
rap-2-h Avatar asked Nov 13 '22 00:11

rap-2-h


1 Answers

Should rather be a comment, but it's more readable this way:

Please execute this query

SELECT CONCAT(table_schema, '.', table_name) AS tablename,
   CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
   CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    data,
   CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
   CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
   ROUND(index_length / data_length, 2)                                           idxfrac
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = database()
AND TABLE_NAME = 'your_table_name'

and check if your table or the index actually fits into memory. If it doesn't...you have the answer.

like image 98
fancyPants Avatar answered Nov 15 '22 07:11

fancyPants