StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMysql Group by query is taking long time
primarykey
Id
16175383
data
AcceptedAnswerId
16176562
AnswerCount
3
ClosedDate
CommentCount
12
CommunityOwnedDate
CreationDate
2013-04-23T17:20:44.010
FavoriteCount
0
LastActivityDate
2013-04-23T18:45:38.807
LastEditDate
2013-04-23T17:56:19.097
LastEditorUserId
796098
OwnerUserId
796098
ParentId
0
PostTypeId
1
Score
0
ViewCount
2819
LastEditorDisplayName
text
Body
I have a table "Words" in mysql database. This table contains 2 fields. word(VARCHAR(256)) and p_id(INTEGER). Create table statement for the table: <pre><code>CREATE TABLE `Words` ( `word` varchar(256) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `p_id` int(11) NOT NULL DEFAULT '0', KEY `word_i` (`word`(255)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; </code></pre> Sample entries in the table are: <pre><code>+------+------+ | word | p_id | +------+------+ | a | 1 | | a | 2 | | b | 1 | | a | 4 | +------+------+ </code></pre> This table contains 30+ million entries in it. I am running a group by query and it is taking 90+ minutes for running that query. The group by query I am running is: <pre><code>SELECT word,group_concat(p_id) FROM Words group by word; </code></pre> To optimize this problem, I sent all the data in the table into a text file using the following query. <pre><code>SELECT p_id,word FROM Words INTO OUTFILE "/tmp/word_map.txt"; </code></pre> After that I wrote a Perl script to read all the content in the file and parse that and make a hash out of it. It took very less time compared to the Group by query(<3min).In the end hash has 14million keys(words). It is occupying a lot of memory.So Is there any way to improve the performance of Group BY query so that I don't need to go through all the above mentioned steps? EDT: I am adding the my.cnf file entries below. <pre><code>[mysqld] datadir=/media/data/.mysql_data/mysql tmpdir=/media/data/.mysql_tmp_data innodb_log_file_size=5M socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 group_concat_max_len=4M max_allowed_packet=20M [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid tmpdir=/media/data/.mysql_tmp_data/ </code></pre> Thanks, Vinod
Tags
<mysql><group-by><group-concat>
Title
Mysql Group by query is taking long time
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USvinod
UserOwnerUserId
1. USvinod
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.