Re: performance suggestions?? mysql_use_result/mysql_store_result - Petr Vileta - com.mysql.lists.win32

8 messages in com.mysql.lists.win32Re: performance suggestions?? mysql_u...

From	Sent On	Attachments
Derick Smith	12 May 2003 12:57
Derick Smith	13 May 2003 06:14
Anthony R. J. Ball	13 May 2003 09:50
Petr Vileta	13 May 2003 15:28
Derick Smith	14 May 2003 08:24
Anthony R. J. Ball	14 May 2003 09:17
Derick Smith	14 May 2003 09:52
Petr Vileta	14 May 2003 15:58

Subject:	Re: performance suggestions?? mysql_use_result/mysql_store_result
From:	Petr Vileta (pe...@practisoft.cz)
Date:	05/14/2003 03:58:41 PM
List:	com.mysql.lists.win32

const char * strCreateTableTEXT = "CREATE TABLE text ( " " tag int(255) NOT NULL auto_increment, " " cpk char(255) binary default NULL, " " cpkovfl tinyint(4) NOT NULL default '0', " " cpko char(176) binary default NULL, " " filter char(11) NOT NULL default '', " " code char(50) NOT NULL default '', " " reftext char(255) NOT NULL default '', " " parsetext char(255) NOT NULL default '', " " dclsnt tinyint(3) unsigned NOT NULL default '0', " " PRIMARY KEY (tag), " " UNIQUE KEY tagidx (tag), " " UNIQUE KEY textidx (parsetext, filter), " " KEY reftextidx (parsetext), " " KEY filteridx (filter), " " KEY cpkidx (cpk), " " KEY cpkovflidx (cpkovfl), " " KEY cpkoidx (cpko), " " KEY codeidx (code), " " UNIQUE KEY cpkallidx (cpk, cpko, cpkovfl), " ") TYPE=MyISAM COMMENT='Text Table'; ";

[...]

SELECT * FROM text WHERE filter = "var1" and reftext = "var2" GROUP BY

code; I don't know why you select * (all fields) if you grouping the result by "code". Think with me: 1.row: tag=10, code='A' 2.row: tag=11, code='A' 3.row: tag=50, code='B' From your query I get result 10,A 50,B Value 11 from 2.row are lost and for this any value of tag is irelevant :-) Test to select only field what you must have. MySQL create smaller temporary file for grouping and this can speed the process. You can use BIG_RESULT or SMALL_RESULT keywords too (see manual). I have no other idea.