From: Daniel F. <dan...@te...> - 2008-08-23 13:42:47
|
It appears that Semantic Drilldown's BrowseDataPage::createTempTable is using a SQL ALTER command. This would be a case of lazy coding. It adds a extra query for something which should be done inside of the create statement, and it also requires that the unprivileged mysql user has the ability to alter the structure of the tables, which would be considered an administrative thing. The ALTER command does not have a TEMPORARY like DROP does, and honestly does not need one since all of the definitions for a temporary table should be specified inside of it's creation. -- ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: -The Nadir-Point Group (http://nadir-point.com) --It's Wiki-Tools subgroup (http://wiki-tools.com) --The ElectronicMe project (http://electronic-me.org) --Games-G.P.S. (http://ggps.org) -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) --Animepedia (http://anime.wikia.com) --Narutopedia (http://naruto.wikia.com) |
From: Yaron K. <ya...@gm...> - 2008-08-25 02:32:26
|
Hi, What SQL code would you suggest in its place? Originally the call was "CREATE INDEX", but this too sometimes led to an access-denied error: https://bugzilla.wikimedia.org/show_bug.cgi?id=13352 -Yaron On Sat, Aug 23, 2008 at 9:42 AM, Daniel Friesen <dan...@te...>wrote: > It appears that Semantic Drilldown's BrowseDataPage::createTempTable is > using a SQL ALTER command. > > This would be a case of lazy coding. It adds a extra query for something > which should be done inside of the create statement, and it also > requires that the unprivileged mysql user has the ability to alter the > structure of the tables, which would be considered an administrative > thing. The ALTER command does not have a TEMPORARY like DROP does, and > honestly does not need one since all of the definitions for a temporary > table should be specified inside of it's creation. > > -- > ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: > -The Nadir-Point Group (http://nadir-point.com) > --It's Wiki-Tools subgroup (http://wiki-tools.com) > --The ElectronicMe project (http://electronic-me.org) > --Games-G.P.S. (http://ggps.org) > -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) > --Animepedia (http://anime.wikia.com) > --Narutopedia (http://naruto.wikia.com) > > > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's > challenge > Build the coolest Linux based applications with Moblin SDK & win great > prizes > Grand prize is a trip for two to an Open Source event anywhere in the world > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > _______________________________________________ > Semediawiki-devel mailing list > Sem...@li... > https://lists.sourceforge.net/lists/listinfo/semediawiki-devel > |
From: Daniel F. <dan...@te...> - 2008-08-25 02:44:15
|
~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: -The Nadir-Point Group (http://nadir-point.com) --It's Wiki-Tools subgroup (http://wiki-tools.com) --The ElectronicMe project (http://electronic-me.org) --Games-G.P.S. (http://ggps.org) -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) --Animepedia (http://anime.wikia.com) --Narutopedia (http://naruto.wikia.com)It would probably be best to create the temporary table with an explicit set of fields, and add the id then. And then, fill the table up with the data. That could be done in a second INSERT ... SELECT ... query, or it may actually be possible to combine everything into the one create query. ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: -The Nadir-Point Group (http://nadir-point.com) --It's Wiki-Tools subgroup (http://wiki-tools.com) --The ElectronicMe project (http://electronic-me.org) --Games-G.P.S. (http://ggps.org) -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) --Animepedia (http://anime.wikia.com) --Narutopedia (http://naruto.wikia.com) Yaron Koren wrote: > Hi, > > What SQL code would you suggest in its place? Originally the call was > "CREATE INDEX", but this too sometimes led to an access-denied error: > > https://bugzilla.wikimedia.org/show_bug.cgi?id=13352 > > -Yaron > > > On Sat, Aug 23, 2008 at 9:42 AM, Daniel Friesen <dan...@te...>wrote: > > >> It appears that Semantic Drilldown's BrowseDataPage::createTempTable is >> using a SQL ALTER command. >> >> This would be a case of lazy coding. It adds a extra query for something >> which should be done inside of the create statement, and it also >> requires that the unprivileged mysql user has the ability to alter the >> structure of the tables, which would be considered an administrative >> thing. The ALTER command does not have a TEMPORARY like DROP does, and >> honestly does not need one since all of the definitions for a temporary >> table should be specified inside of it's creation. >> >> -- >> ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: >> -The Nadir-Point Group (http://nadir-point.com) >> --It's Wiki-Tools subgroup (http://wiki-tools.com) >> --The ElectronicMe project (http://electronic-me.org) >> --Games-G.P.S. (http://ggps.org) >> -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) >> --Animepedia (http://anime.wikia.com) >> --Narutopedia (http://naruto.wikia.com) >> >> >> ------------------------------------------------------------------------- >> This SF.Net email is sponsored by the Moblin Your Move Developer's >> challenge >> Build the coolest Linux based applications with Moblin SDK & win great >> prizes >> Grand prize is a trip for two to an Open Source event anywhere in the world >> http://moblin-contest.org/redirect.php?banner_id=100&url=/ >> _______________________________________________ >> Semediawiki-devel mailing list >> Sem...@li... >> https://lists.sourceforge.net/lists/listinfo/semediawiki-devel >> >> > > |
From: Yaron K. <ya...@gm...> - 2008-08-27 18:57:58
|
Sorry, can you elaborate on this? I get the sense you're talking about a SQL call like: CREATE TEMPORARY TABLE semantic_drilldown_values (...set of columns...) ...some "CREATE INDEX" or "ALTER TABLE" call... INSERT INTO semantic_drilldown_values (...SQL call...) If that's right, how would this require fewer permissions than the current code? -Yaron On Sun, Aug 24, 2008 at 10:43 PM, Daniel Friesen <dan...@te...>wrote: > > ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: > -The Nadir-Point Group (http://nadir-point.com) > --It's Wiki-Tools subgroup (http://wiki-tools.com) > --The ElectronicMe project (http://electronic-me.org) > --Games-G.P.S. (http://ggps.org) > -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) > --Animepedia (http://anime.wikia.com) > --Narutopedia (http://naruto.wikia.com)It would probably be best to > create the temporary table with an explicit > set of fields, and add the id then. And then, fill the table up with the > data. That could be done in a second INSERT ... SELECT ... query, or it > may actually be possible to combine everything into the one create query. > > ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: > -The Nadir-Point Group (http://nadir-point.com) > --It's Wiki-Tools subgroup (http://wiki-tools.com) > --The ElectronicMe project (http://electronic-me.org) > --Games-G.P.S. (http://ggps.org) > -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) > --Animepedia (http://anime.wikia.com) > --Narutopedia (http://naruto.wikia.com) > > Yaron Koren wrote: > > Hi, > > > > What SQL code would you suggest in its place? Originally the call was > > "CREATE INDEX", but this too sometimes led to an access-denied error: > > > > https://bugzilla.wikimedia.org/show_bug.cgi?id=13352 > > > > -Yaron > > > > > > On Sat, Aug 23, 2008 at 9:42 AM, Daniel Friesen <dan...@te... > >wrote: > > > > > >> It appears that Semantic Drilldown's BrowseDataPage::createTempTable is > >> using a SQL ALTER command. > >> > >> This would be a case of lazy coding. It adds a extra query for something > >> which should be done inside of the create statement, and it also > >> requires that the unprivileged mysql user has the ability to alter the > >> structure of the tables, which would be considered an administrative > >> thing. The ALTER command does not have a TEMPORARY like DROP does, and > >> honestly does not need one since all of the definitions for a temporary > >> table should be specified inside of it's creation. > >> > >> -- > >> ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: > >> -The Nadir-Point Group (http://nadir-point.com) > >> --It's Wiki-Tools subgroup (http://wiki-tools.com) > >> --The ElectronicMe project (http://electronic-me.org) > >> --Games-G.P.S. (http://ggps.org) > >> -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) > >> --Animepedia (http://anime.wikia.com) > >> --Narutopedia (http://naruto.wikia.com) > >> > >> > >> > ------------------------------------------------------------------------- > >> This SF.Net email is sponsored by the Moblin Your Move Developer's > >> challenge > >> Build the coolest Linux based applications with Moblin SDK & win great > >> prizes > >> Grand prize is a trip for two to an Open Source event anywhere in the > world > >> http://moblin-contest.org/redirect.php?banner_id=100&url=/ > >> _______________________________________________ > >> Semediawiki-devel mailing list > >> Sem...@li... > >> https://lists.sourceforge.net/lists/listinfo/semediawiki-devel > >> > >> > > > > > > > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's > challenge > Build the coolest Linux based applications with Moblin SDK & win great > prizes > Grand prize is a trip for two to an Open Source event anywhere in the world > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > _______________________________________________ > Semediawiki-devel mailing list > Sem...@li... > https://lists.sourceforge.net/lists/listinfo/semediawiki-devel > |
From: Daniel F. <dan...@te...> - 2008-08-28 04:21:52
|
It's all wrapped into the one query. It's the 'ALTER' itself that is the issue. Even when modifying a temporary table the sql user needs to have use of the ALTER command (Which by default is not granted by MediaWiki). Quite simply, because everything is part of the CREATE TEMPORARY and there is no ALTER command, then the only permission needed is CREATE TEMPORARY TABLES, which MediaWiki does grant by default. ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: -The Nadir-Point Group (http://nadir-point.com) --It's Wiki-Tools subgroup (http://wiki-tools.com) --The ElectronicMe project (http://electronic-me.org) --Games-G.P.S. (http://ggps.org) -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) --Animepedia (http://anime.wikia.com) --Narutopedia (http://naruto.wikia.com) Yaron Koren wrote: > Sorry, can you elaborate on this? I get the sense you're talking about a SQL > call like: > > CREATE TEMPORARY TABLE semantic_drilldown_values (...set of columns...) > ...some "CREATE INDEX" or "ALTER TABLE" call... > INSERT INTO semantic_drilldown_values (...SQL call...) > > If that's right, how would this require fewer permissions than the current > code? > > -Yaron > > > On Sun, Aug 24, 2008 at 10:43 PM, Daniel Friesen <dan...@te...>wrote: > > >> ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: >> -The Nadir-Point Group (http://nadir-point.com) >> --It's Wiki-Tools subgroup (http://wiki-tools.com) >> --The ElectronicMe project (http://electronic-me.org) >> --Games-G.P.S. (http://ggps.org) >> -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) >> --Animepedia (http://anime.wikia.com) >> --Narutopedia (http://naruto.wikia.com)It would probably be best to >> create the temporary table with an explicit >> set of fields, and add the id then. And then, fill the table up with the >> data. That could be done in a second INSERT ... SELECT ... query, or it >> may actually be possible to combine everything into the one create query. >> >> ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: >> -The Nadir-Point Group (http://nadir-point.com) >> --It's Wiki-Tools subgroup (http://wiki-tools.com) >> --The ElectronicMe project (http://electronic-me.org) >> --Games-G.P.S. (http://ggps.org) >> -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) >> --Animepedia (http://anime.wikia.com) >> --Narutopedia (http://naruto.wikia.com) >> >> Yaron Koren wrote: >> >>> Hi, >>> >>> What SQL code would you suggest in its place? Originally the call was >>> "CREATE INDEX", but this too sometimes led to an access-denied error: >>> >>> https://bugzilla.wikimedia.org/show_bug.cgi?id=13352 >>> >>> -Yaron >>> >>> >>> On Sat, Aug 23, 2008 at 9:42 AM, Daniel Friesen <dan...@te... >>> wrote: >>> >>> >>> >>>> It appears that Semantic Drilldown's BrowseDataPage::createTempTable is >>>> using a SQL ALTER command. >>>> >>>> This would be a case of lazy coding. It adds a extra query for something >>>> which should be done inside of the create statement, and it also >>>> requires that the unprivileged mysql user has the ability to alter the >>>> structure of the tables, which would be considered an administrative >>>> thing. The ALTER command does not have a TEMPORARY like DROP does, and >>>> honestly does not need one since all of the definitions for a temporary >>>> table should be specified inside of it's creation. >>>> >>>> -- >>>> ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: >>>> -The Nadir-Point Group (http://nadir-point.com) >>>> --It's Wiki-Tools subgroup (http://wiki-tools.com) >>>> --The ElectronicMe project (http://electronic-me.org) >>>> --Games-G.P.S. (http://ggps.org) >>>> -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) >>>> --Animepedia (http://anime.wikia.com) >>>> --Narutopedia (http://naruto.wikia.com) >>>> >>>> >>>> >>>> >> ------------------------------------------------------------------------- >> >>>> This SF.Net email is sponsored by the Moblin Your Move Developer's >>>> challenge >>>> Build the coolest Linux based applications with Moblin SDK & win great >>>> prizes >>>> Grand prize is a trip for two to an Open Source event anywhere in the >>>> >> world >> >>>> http://moblin-contest.org/redirect.php?banner_id=100&url=/ >>>> _______________________________________________ >>>> Semediawiki-devel mailing list >>>> Sem...@li... >>>> https://lists.sourceforge.net/lists/listinfo/semediawiki-devel >>>> >>>> >>>> >>> >> ------------------------------------------------------------------------- >> This SF.Net email is sponsored by the Moblin Your Move Developer's >> challenge >> Build the coolest Linux based applications with Moblin SDK & win great >> prizes >> Grand prize is a trip for two to an Open Source event anywhere in the world >> http://moblin-contest.org/redirect.php?banner_id=100&url=/ >> _______________________________________________ >> Semediawiki-devel mailing list >> Sem...@li... >> https://lists.sourceforge.net/lists/listinfo/semediawiki-devel >> >> > > > ------------------------------------------------------------------------ > > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's challenge > Build the coolest Linux based applications with Moblin SDK & win great prizes > Grand prize is a trip for two to an Open Source event anywhere in the world > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > ------------------------------------------------------------------------ > > _______________________________________________ > Semediawiki-devel mailing list > Sem...@li... > https://lists.sourceforge.net/lists/listinfo/semediawiki-devel > |
From: Yaron K. <ya...@gm...> - 2008-08-28 11:59:06
|
Yes, I understand - sorry, I should have been clearer: the question is, how does one add an index as part of a "CREATE TABLE" statement? -Yaron On Thu, Aug 28, 2008 at 12:21 AM, Daniel Friesen <dan...@te...>wrote: > It's all wrapped into the one query. It's the 'ALTER' itself that is the > issue. Even when modifying a temporary table the sql user needs to have use > of the ALTER command (Which by default is not granted by MediaWiki). Quite > simply, because everything is part of the CREATE TEMPORARY and there is no > ALTER command, then the only permission needed is CREATE TEMPORARY TABLES, > which MediaWiki does grant by default. > > ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: > -The Nadir-Point Group (http://nadir-point.com) > --It's Wiki-Tools subgroup (http://wiki-tools.com) > --The ElectronicMe project (http://electronic-me.org) > --Games-G.P.S. (http://ggps.org) > -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) > --Animepedia (http://anime.wikia.com) > --Narutopedia (http://naruto.wikia.com) > > Yaron Koren wrote: > > Sorry, can you elaborate on this? I get the sense you're talking about a SQL > call like: > > CREATE TEMPORARY TABLE semantic_drilldown_values (...set of columns...) > ...some "CREATE INDEX" or "ALTER TABLE" call... > INSERT INTO semantic_drilldown_values (...SQL call...) > > If that's right, how would this require fewer permissions than the current > code? > > -Yaron > > > On Sun, Aug 24, 2008 at 10:43 PM, Daniel Friesen <dan...@te...> <dan...@te...>wrote: > > > > ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: > -The Nadir-Point Group (http://nadir-point.com) > --It's Wiki-Tools subgroup (http://wiki-tools.com) > --The ElectronicMe project (http://electronic-me.org) > --Games-G.P.S. (http://ggps.org) > -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) > --Animepedia (http://anime.wikia.com) > --Narutopedia (http://naruto.wikia.com)It would probably be best to > create the temporary table with an explicit > set of fields, and add the id then. And then, fill the table up with the > data. That could be done in a second INSERT ... SELECT ... query, or it > may actually be possible to combine everything into the one create query. > > ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: > -The Nadir-Point Group (http://nadir-point.com) > --It's Wiki-Tools subgroup (http://wiki-tools.com) > --The ElectronicMe project (http://electronic-me.org) > --Games-G.P.S. (http://ggps.org) > -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) > --Animepedia (http://anime.wikia.com) > --Narutopedia (http://naruto.wikia.com) > > Yaron Koren wrote: > > > Hi, > > What SQL code would you suggest in its place? Originally the call was > "CREATE INDEX", but this too sometimes led to an access-denied error: > https://bugzilla.wikimedia.org/show_bug.cgi?id=13352 > > -Yaron > > > On Sat, Aug 23, 2008 at 9:42 AM, Daniel Friesen <dan...@te... > wrote: > > > > > It appears that Semantic Drilldown's BrowseDataPage::createTempTable is > using a SQL ALTER command. > > This would be a case of lazy coding. It adds a extra query for something > which should be done inside of the create statement, and it also > requires that the unprivileged mysql user has the ability to alter the > structure of the tables, which would be considered an administrative > thing. The ALTER command does not have a TEMPORARY like DROP does, and > honestly does not need one since all of the definitions for a temporary > table should be specified inside of it's creation. > > -- > ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: > -The Nadir-Point Group (http://nadir-point.com) > --It's Wiki-Tools subgroup (http://wiki-tools.com) > --The ElectronicMe project (http://electronic-me.org) > --Games-G.P.S. (http://ggps.org) > -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) > --Animepedia (http://anime.wikia.com) > --Narutopedia (http://naruto.wikia.com) > > > > > > ------------------------------------------------------------------------- > > > This SF.Net email is sponsored by the Moblin Your Move Developer's > challenge > Build the coolest Linux based applications with Moblin SDK & win great > prizes > Grand prize is a trip for two to an Open Source event anywhere in the > > > world > > > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > _______________________________________________ > Semediawiki-devel mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/semediawiki-devel > > > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's > challenge > Build the coolest Linux based applications with Moblin SDK & win great > prizes > Grand prize is a trip for two to an Open Source event anywhere in the worldhttp://moblin-contest.org/redirect.php?banner_id=100&url=/ > _______________________________________________ > Semediawiki-devel mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/semediawiki-devel > > ------------------------------ > > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's challenge > Build the coolest Linux based applications with Moblin SDK & win great prizes > Grand prize is a trip for two to an Open Source event anywhere in the worldhttp://moblin-contest.org/redirect.php?banner_id=100&url=/ > > ------------------------------ > > _______________________________________________ > Semediawiki-devel mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/semediawiki-devel > > > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's > challenge > Build the coolest Linux based applications with Moblin SDK & win great > prizes > Grand prize is a trip for two to an Open Source event anywhere in the world > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > _______________________________________________ > Semediawiki-devel mailing list > Sem...@li... > https://lists.sourceforge.net/lists/listinfo/semediawiki-devel > > |
From: Daniel F. <dan...@te...> - 2008-08-28 19:33:01
|
Well, to start... what is the structure of the table that the current query creates? ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: -The Nadir-Point Group (http://nadir-point.com) --It's Wiki-Tools subgroup (http://wiki-tools.com) --The ElectronicMe project (http://electronic-me.org) --Games-G.P.S. (http://ggps.org) -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) --Animepedia (http://anime.wikia.com) --Narutopedia (http://naruto.wikia.com) Yaron Koren wrote: > Yes, I understand - sorry, I should have been clearer: the question is, how > does one add an index as part of a "CREATE TABLE" statement? > > -Yaron > > On Thu, Aug 28, 2008 at 12:21 AM, Daniel Friesen <dan...@te...>wrote: > > >> It's all wrapped into the one query. It's the 'ALTER' itself that is the >> issue. Even when modifying a temporary table the sql user needs to have use >> of the ALTER command (Which by default is not granted by MediaWiki). Quite >> simply, because everything is part of the CREATE TEMPORARY and there is no >> ALTER command, then the only permission needed is CREATE TEMPORARY TABLES, >> which MediaWiki does grant by default. >> >> ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: >> -The Nadir-Point Group (http://nadir-point.com) >> --It's Wiki-Tools subgroup (http://wiki-tools.com) >> --The ElectronicMe project (http://electronic-me.org) >> --Games-G.P.S. (http://ggps.org) >> -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) >> --Animepedia (http://anime.wikia.com) >> --Narutopedia (http://naruto.wikia.com) >> >> Yaron Koren wrote: >> >> Sorry, can you elaborate on this? I get the sense you're talking about a SQL >> call like: >> >> CREATE TEMPORARY TABLE semantic_drilldown_values (...set of columns...) >> ...some "CREATE INDEX" or "ALTER TABLE" call... >> INSERT INTO semantic_drilldown_values (...SQL call...) >> >> If that's right, how would this require fewer permissions than the current >> code? >> >> -Yaron >> >> >> On Sun, Aug 24, 2008 at 10:43 PM, Daniel Friesen <dan...@te...> <dan...@te...>wrote: >> >> >> >> ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: >> -The Nadir-Point Group (http://nadir-point.com) >> --It's Wiki-Tools subgroup (http://wiki-tools.com) >> --The ElectronicMe project (http://electronic-me.org) >> --Games-G.P.S. (http://ggps.org) >> -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) >> --Animepedia (http://anime.wikia.com) >> --Narutopedia (http://naruto.wikia.com)It would probably be best to >> create the temporary table with an explicit >> set of fields, and add the id then. And then, fill the table up with the >> data. That could be done in a second INSERT ... SELECT ... query, or it >> may actually be possible to combine everything into the one create query. >> >> ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: >> -The Nadir-Point Group (http://nadir-point.com) >> --It's Wiki-Tools subgroup (http://wiki-tools.com) >> --The ElectronicMe project (http://electronic-me.org) >> --Games-G.P.S. (http://ggps.org) >> -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) >> --Animepedia (http://anime.wikia.com) >> --Narutopedia (http://naruto.wikia.com) >> >> Yaron Koren wrote: >> >> >> Hi, >> >> What SQL code would you suggest in its place? Originally the call was >> "CREATE INDEX", but this too sometimes led to an access-denied error: >> https://bugzilla.wikimedia.org/show_bug.cgi?id=13352 >> >> -Yaron >> >> >> On Sat, Aug 23, 2008 at 9:42 AM, Daniel Friesen <dan...@te... >> wrote: >> >> >> >> >> It appears that Semantic Drilldown's BrowseDataPage::createTempTable is >> using a SQL ALTER command. >> >> This would be a case of lazy coding. It adds a extra query for something >> which should be done inside of the create statement, and it also >> requires that the unprivileged mysql user has the ability to alter the >> structure of the tables, which would be considered an administrative >> thing. The ALTER command does not have a TEMPORARY like DROP does, and >> honestly does not need one since all of the definitions for a temporary >> table should be specified inside of it's creation. >> >> -- >> ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: >> -The Nadir-Point Group (http://nadir-point.com) >> --It's Wiki-Tools subgroup (http://wiki-tools.com) >> --The ElectronicMe project (http://electronic-me.org) >> --Games-G.P.S. (http://ggps.org) >> -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) >> --Animepedia (http://anime.wikia.com) >> --Narutopedia (http://naruto.wikia.com) >> >> >> >> >> >> ------------------------------------------------------------------------- >> >> >> This SF.Net email is sponsored by the Moblin Your Move Developer's >> challenge >> Build the coolest Linux based applications with Moblin SDK & win great >> prizes >> Grand prize is a trip for two to an Open Source event anywhere in the >> >> >> world >> >> >> http://moblin-contest.org/redirect.php?banner_id=100&url=/ >> _______________________________________________ >> Semediawiki-devel mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/semediawiki-devel >> >> >> ------------------------------------------------------------------------- >> This SF.Net email is sponsored by the Moblin Your Move Developer's >> challenge >> Build the coolest Linux based applications with Moblin SDK & win great >> prizes >> Grand prize is a trip for two to an Open Source event anywhere in the worldhttp://moblin-contest.org/redirect.php?banner_id=100&url=/ >> _______________________________________________ >> Semediawiki-devel mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/semediawiki-devel >> >> ------------------------------ >> >> ------------------------------------------------------------------------- >> This SF.Net email is sponsored by the Moblin Your Move Developer's challenge >> Build the coolest Linux based applications with Moblin SDK & win great prizes >> Grand prize is a trip for two to an Open Source event anywhere in the worldhttp://moblin-contest.org/redirect.php?banner_id=100&url=/ >> >> ------------------------------ >> >> _______________________________________________ >> Semediawiki-devel mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/semediawiki-devel >> >> >> ------------------------------------------------------------------------- >> This SF.Net email is sponsored by the Moblin Your Move Developer's >> challenge >> Build the coolest Linux based applications with Moblin SDK & win great >> prizes >> Grand prize is a trip for two to an Open Source event anywhere in the world >> http://moblin-contest.org/redirect.php?banner_id=100&url=/ >> _______________________________________________ >> Semediawiki-devel mailing list >> Sem...@li... >> https://lists.sourceforge.net/lists/listinfo/semediawiki-devel >> >> >> > > > ------------------------------------------------------------------------ > > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's challenge > Build the coolest Linux based applications with Moblin SDK & win great prizes > Grand prize is a trip for two to an Open Source event anywhere in the world > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > ------------------------------------------------------------------------ > > _______________________________________________ > Semediawiki-devel mailing list > Sem...@li... > https://lists.sourceforge.net/lists/listinfo/semediawiki-devel > |
From: Yaron K. <ya...@gm...> - 2008-08-28 20:33:50
|
Well, actually, "ALTER TABLE" is currently called in two different places in the code, to add an index to two different temp tables: one holds just an integer column, while the other holds an integer and a string column. In both cases, the index is being added to the integer column. I suppose the table could just be sorted on that column in both cases; I don't know why I didn't do that. Would that have the same performance effect as indexing? -Yaron On Thu, Aug 28, 2008 at 3:32 PM, Daniel Friesen <dan...@te...>wrote: > Well, to start... what is the structure of the table that the current > query creates? > > |
From: Daniel F. <dan...@te...> - 2008-08-28 21:01:24
|
Sorting really doesn't make much different... The index is what stops mysql from reading through the entire table instead of just grabbing a few needed rows. CREATE TEMPORARY TABLE semantic_drilldown_values ( id INT NOT NULL, INDEX id_index (id) ) AS SELECT ids.smw_id AS id FROM ... Something along that lines. Basically instead of doing the SELECT statement, and having it assume what kind of table to create, you give it the structure then let the SELECT fill THAT table instead of being used to understand what kind of table to create. And in the table's structure you add the definition for the index. ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: -The Nadir-Point Group (http://nadir-point.com) --It's Wiki-Tools subgroup (http://wiki-tools.com) --The ElectronicMe project (http://electronic-me.org) --Games-G.P.S. (http://ggps.org) -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) --Animepedia (http://anime.wikia.com) --Narutopedia (http://naruto.wikia.com) Yaron Koren wrote: > Well, actually, "ALTER TABLE" is currently called in two different places in > the code, to add an index to two different temp tables: one holds just an > integer column, while the other holds an integer and a string column. In > both cases, the index is being added to the integer column. I suppose the > table could just be sorted on that column in both cases; I don't know why I > didn't do that. Would that have the same performance effect as indexing? > > -Yaron > > On Thu, Aug 28, 2008 at 3:32 PM, Daniel Friesen <dan...@te...>wrote: > > >> Well, to start... what is the structure of the table that the current >> query creates? >> >> >> > > > ------------------------------------------------------------------------ > > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's challenge > Build the coolest Linux based applications with Moblin SDK & win great prizes > Grand prize is a trip for two to an Open Source event anywhere in the world > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > ------------------------------------------------------------------------ > > _______________________________________________ > Semediawiki-devel mailing list > Sem...@li... > https://lists.sourceforge.net/lists/listinfo/semediawiki-devel > |
From: Yaron K. <ya...@gm...> - 2008-08-28 21:13:31
|
Ah, that "INDEX" call is what I was looking for. I'm trying it out now, and it seems to work well, thanks. -Yaron On Thu, Aug 28, 2008 at 5:00 PM, Daniel Friesen <dan...@te...>wrote: > Sorting really doesn't make much different... The index is what stops > mysql from reading through the entire table instead of just grabbing a few > needed rows. > > CREATE TEMPORARY TABLE semantic_drilldown_values ( > id INT NOT NULL, > INDEX id_index (id) > ) AS SELECT ids.smw_id AS id FROM ... > > Something along that lines. Basically instead of doing the SELECT > statement, and having it assume what kind of table to create, you give it > the structure then let the SELECT fill THAT table instead of being used to > understand what kind of table to create. And in the table's structure you > add the definition for the index. > > ~Daniel Friesen(Dantman, Nadir-Seen-Fire) of: > -The Nadir-Point Group (http://nadir-point.com) > --It's Wiki-Tools subgroup (http://wiki-tools.com) > --The ElectronicMe project (http://electronic-me.org) > --Games-G.P.S. (http://ggps.org) > -And Wikia ACG on Wikia.com (http://wikia.com/wiki/Wikia_ACG) > --Animepedia (http://anime.wikia.com) > --Narutopedia (http://naruto.wikia.com) > > Yaron Koren wrote: > > Well, actually, "ALTER TABLE" is currently called in two different places in > the code, to add an index to two different temp tables: one holds just an > integer column, while the other holds an integer and a string column. In > both cases, the index is being added to the integer column. I suppose the > table could just be sorted on that column in both cases; I don't know why I > didn't do that. Would that have the same performance effect as indexing? > > -Yaron > > On Thu, Aug 28, 2008 at 3:32 PM, Daniel Friesen <dan...@te...> <dan...@te...>wrote: > > > > Well, to start... what is the structure of the table that the current > query creates? > > > > > ------------------------------ > > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's challenge > Build the coolest Linux based applications with Moblin SDK & win great prizes > Grand prize is a trip for two to an Open Source event anywhere in the worldhttp://moblin-contest.org/redirect.php?banner_id=100&url=/ > > ------------------------------ > > _______________________________________________ > Semediawiki-devel mailing lis...@li...https://lists.sourceforge.net/lists/listinfo/semediawiki-devel > > > ------------------------------------------------------------------------- > This SF.Net email is sponsored by the Moblin Your Move Developer's > challenge > Build the coolest Linux based applications with Moblin SDK & win great > prizes > Grand prize is a trip for two to an Open Source event anywhere in the world > http://moblin-contest.org/redirect.php?banner_id=100&url=/ > _______________________________________________ > Semediawiki-devel mailing list > Sem...@li... > https://lists.sourceforge.net/lists/listinfo/semediawiki-devel > > |