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`;
I do not understand why my query uses temporary... and I don't know how to avoid this... Thanks
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With