Hello, All!
Can anyone suggest how to improve the performance of a query like this:
CREATE TABLE u_mashiah.pagelinks ( `pl_from` int(8) unsigned NOT NULL default '0', `pl_namespace` int(11) NOT NULL default '0', `pl_title` varchar(255) binary NOT NULL default '', KEY `pl_from` (`pl_from`,`pl_namespace`) ) TYPE=MyISAM AS /* SLOW_OK */ SELECT pl_from, pl_namespace, pl_title FROM ruwiki_p.pagelinks;
This is the complete copying of a table from readonly database to personal one with some altering on keys, not sufficient I suppose. It looks like if copying like this is much slower than access to the same amount of data in one database.
Please, advise.
Mashiah
Mashiah Davidson skrev:
Hello, All!
Can anyone suggest how to improve the performance of a query like this:
CREATE TABLE u_mashiah.pagelinks ( `pl_from` int(8) unsigned NOT NULL default '0', `pl_namespace` int(11) NOT NULL default '0', `pl_title` varchar(255) binary NOT NULL default '', KEY `pl_from` (`pl_from`,`pl_namespace`) ) TYPE=MyISAM AS /* SLOW_OK */ SELECT pl_from, pl_namespace, pl_title FROM ruwiki_p.pagelinks;
This is the complete copying of a table from readonly database to personal one with some altering on keys, not sufficient I suppose. It looks like if copying like this is much slower than access to the same amount of data in one database.
Please, advise.
Create the table without keys and add them later as needed when the table is populated. It is much faster insert when there is no keys to update for each inserted row.
Bst regards Byrial
Thank you, Byrial,
I have tried your suggestion and now I can say that nothing has changed, the query needs thousands of seconds to complete like if the data comes from sql host to hemlock and then goes back through the network when I select from one database to another one. Maybe I'm wrong, but on my local machine with very slow harddrive and cpu the very similar query creating the same table from a file work several times faster and other queries are usually slower.
Mashiah
2007/6/20, Byrial Jensen byrial@vip.cybercity.dk:
Mashiah Davidson skrev:
Hello, All!
Can anyone suggest how to improve the performance of a query like this:
CREATE TABLE u_mashiah.pagelinks ( `pl_from` int(8) unsigned NOT NULL default '0', `pl_namespace` int(11) NOT NULL default '0', `pl_title` varchar(255) binary NOT NULL default '', KEY `pl_from` (`pl_from`,`pl_namespace`) ) TYPE=MyISAM AS /* SLOW_OK */ SELECT pl_from, pl_namespace, pl_title FROM ruwiki_p.pagelinks;
This is the complete copying of a table from readonly database to personal one with some altering on keys, not sufficient I suppose. It looks like if copying like this is much slower than access to the same amount of data in one database.
Please, advise.
Create the table without keys and add them later as needed when the table is populated. It is much faster insert when there is no keys to update for each inserted row.
Bst regards Byrial
Toolserver-l mailing list Toolserver-l@lists.wikimedia.org http://lists.wikimedia.org/mailman/listinfo/toolserver-l
toolserver-l@lists.wikimedia.org