Re: Maximum length of mySQL request ?
There may be an environment variable you need to set to increase it. Because it's network based, it stands to reason that an unlimited query string can be exploited as a denial of service strategy. Bob S > On May 24, 2022, at 10:19 , doc hawk via use-livecode > wrote: > > When opening a “file”, my software makes a compound query with several > hundred queries within it, so . . . > > This is possible on Postgres and SQLite, but (last I checked) not on MySQL ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Maximum length of mySQL request ?
When opening a “file”, my software makes a compound query with several hundred queries within it, so . . . This is possible on Postgres and SQLite, but (last I checked) not on MySQL ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Maximum length of mySQL request ?
OIC you are searching arrays to obtain your IDs. That would make sense because arrays are memory based. SQL is file based as in sqLite, or else accessed (typically) over a network which would account for the speed disparity. Of course, to be fail, you will have to add the time to search the arrays, THEN the time to query your SQL database with the IDs to make that comparison equal. Bob S > On May 20, 2022, at 08:56 , jbv via use-livecode > wrote: > > Le 2022-05-20 11:24, Bob Sneidar via use-livecode a écrit : >> I googled around a bit and there doesn't seem to be a limit on >> statement length. I will say though that the nature of the query >> indicates a possible design issue with the database. I suspect however >> that you are at the mercy of someone else's schema. That there is no >> common denominator you can query on is to me remarkable. How are you >> obtaining your IDs then? > > Thank you for your reply. > I also did some google search before asking the question, but didn't > find anything, only about the size of the data returned by mySQL. > > What makes you think of a possible design issue ? > The ids are auto-incremented every time a new entry is added to the > table, and the ids in the request are obtained by intersecting various > multi-dimensional arrays that make very complex searches very fast > (less than 1 sec, when it takes 5 to 10 sec with regular SQL queries). > > ___ > use-livecode mailing list > use-livecode@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Maximum length of mySQL request ?
Le 2022-05-20 11:24, Bob Sneidar via use-livecode a écrit : I googled around a bit and there doesn't seem to be a limit on statement length. I will say though that the nature of the query indicates a possible design issue with the database. I suspect however that you are at the mercy of someone else's schema. That there is no common denominator you can query on is to me remarkable. How are you obtaining your IDs then? Thank you for your reply. I also did some google search before asking the question, but didn't find anything, only about the size of the data returned by mySQL. What makes you think of a possible design issue ? The ids are auto-incremented every time a new entry is added to the table, and the ids in the request are obtained by intersecting various multi-dimensional arrays that make very complex searches very fast (less than 1 sec, when it takes 5 to 10 sec with regular SQL queries). ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Maximum length of mySQL request ?
Le 2022-05-20 08:00, Paul Dupuis via use-livecode a écrit : You can save some characters by the following: SELECT ... FROM myTable WHERE id IN (5523,7831,162814,34895,...) which is the same as using multiple OR equals in your example. Thank you. Yes I will try that. And I have no idea why it didn't cross my mind... Perhaps because it's friday... :) jbv ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Maximum length of mySQL request ?
I googled around a bit and there doesn't seem to be a limit on statement length. I will say though that the nature of the query indicates a possible design issue with the database. I suspect however that you are at the mercy of someone else's schema. That there is no common denominator you can query on is to me remarkable. How are you obtaining your IDs then? Bob S > On May 20, 2022, at 03:34 , jbv via use-livecode > wrote: > > Hi list, > > Is there a maximum length limit for mySQL requests ? > I don't mean the results returned by mySQL, but the request itself. > > For instance, I have a table with 18 entries and counting, and > I will need to send requests such as : > SELECT FROM myTable WHERE id = 5523 OR id = 7831 OR id = 162814 OR id = > 34895... > and at times the request might concern 100 ids or even more. > > I am not too concerned about execution speed, as id is a unique key, > but rather for the request itself : I wonder if it might be truncated > if too long... > Or perhaps a better and safer practice would be to break it into a serie of > shorter successive requests when it exceeds a certain threshold ? > > Thank you in advance. > jbv > > ___ > use-livecode mailing list > use-livecode@lists.runrev.com > Please visit this url to subscribe, unsubscribe and manage your subscription > preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
Re: Maximum length of mySQL request ?
You can save some characters by the following: SELECT ... FROM myTable WHERE id IN (5523,7831,162814,34895,...) which is the same as using multiple OR equals in your example. If you need to break this up further, you can UNION the results of multiple queries, such as: SELECT ... FROM myTable WHERE id IN (5523,7831,162814,34895,...) -- i.e. the first 100 or 1000 ids UNION SELECT ... FROM myTable WHERE id IN (100,512,,16545,...) -- the next 100 or 1000 ids UNION ... repeat as needed On 5/20/2022 6:34 AM, jbv via use-livecode wrote: Hi list, Is there a maximum length limit for mySQL requests ? I don't mean the results returned by mySQL, but the request itself. For instance, I have a table with 18 entries and counting, and I will need to send requests such as : SELECT FROM myTable WHERE id = 5523 OR id = 7831 OR id = 162814 OR id = 34895... and at times the request might concern 100 ids or even more. I am not too concerned about execution speed, as id is a unique key, but rather for the request itself : I wonder if it might be truncated if too long... Or perhaps a better and safer practice would be to break it into a serie of shorter successive requests when it exceeds a certain threshold ? Thank you in advance. jbv ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode ___ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
