Query Question

------=_NextPart_000_0081_01CA1FF9.37BB73F0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

I am in need of some help for the following:



Say I have a table with 1M rows. Users are being added constantly (not
deleted) during the queries that I am about to explain. The pk is uid and
appid. I need to run queries in increments of 100K rows until reaching the
end without duplicating rows in the queries. I am using a select statement
with a limit of row_index and row_count. This start row is where my
question arises.



If I make a query with limit 0,100000 then 2 minutes later 100000,100000
then 2minutes later 300000,100000 and so on. My question is are new rows
added to the end of the table or will they randomly appear in my queries?
If they are added to the end of the table, that is fine because I will pick
them up in my final pass.



I hope this is clear enough. If not, let me know and I will provide more
information. Thanks!


------=_NextPart_000_0081_01CA1FF9.37BB73F0--

Re: Query Question

--001636833e7e5eb99b04716d53a5
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Bill,

if you use an order by clause in your query, the limit will pick the first
100K rows in that order. That way you can ensure that all rows will be
processed in (wait for it...) order :)

Cheers,

Walter

On Tue, Aug 18, 2009 at 18:44, Bill Arbuckle wrote:

> I am in need of some help for the following:
>
>
>
> Say I have a table with 1M rows. Users are being added constantly (not
> deleted) during the queries that I am about to explain. The pk is uid and
> appid. I need to run queries in increments of 100K rows until reaching the
> end without duplicating rows in the queries. I am using a select statement
> with a limit of row_index and row_count. This start row is where my
> question arises.
>
>
>
> If I make a query with limit 0,100000 then 2 minutes later 100000,100000
> then 2minutes later 300000,100000 and so on. My question is are new rows
> added to the end of the table or will they randomly appear in my queries?
> If they are added to the end of the table, that is fine because I will pick
> them up in my final pass.
>
>
>
> I hope this is clear enough. If not, let me know and I will provide more
> information. Thanks!
>
>


--
Walter Heck, Engineer @ Open Query (http://openquery.com)
Affordable Training and ProActive Support for MySQL & related technologies

Follow our blog at http://openquery.com/blog/
OurDelta: free enhanced builds for MySQL @ http://ourdelta.org

--001636833e7e5eb99b04716d53a5--

RE: Query Question

VG8gZnVydGhlciBlbXBoYXNpemUgdGhpcyBwb2ludDogIEEgdGFibGUgaGFz IG5vIG9yZGVyIGJ5
IGl0c2VsZiwgYW5kIHlvdSBzaG91bGQgbWFrZSBubyBhc3N1bXB0aW9ucyBh Ym91dCB0aGUgb3Jk
ZXIgb2Ygcm93cyB5b3Ugd2lsbCBnZXQgYmFjayBpbiBhIHNlbGVjdCBzdGF0 ZW1lbnQsIHVubGVz
cyB5b3UgdXNlIGFuIE9SREVSIEJZIGNsYXVzZS4NCg0KUmVnYXJkcywNCkdh dmluIFRvd2V5DQoN
Ci0tLS0tT3JpZ2luYWwgTWVzc2FnZS0tLS0tDQpGcm9tOiB3YWx0ZXJoZWNr QGdtYWlsLmNvbSBb
bWFpbHRvOndhbHRlcmhlY2tAZ21haWwuY29tXSBPbiBCZWhhbGYgT2YgV2Fs dGVyIEhlY2sgLSBP
bGluRGF0YS5jb20NClNlbnQ6IFR1ZXNkYXksIEF1Z3VzdCAxOCwgMjAwOSA5 OjUxIEFNDQpUbzog
YmlsbEBhcmJ1Y2tsZWxsYy5jb20NCkNjOiBteXNxbEBsaXN0cy5teXNxbC5j b20NClN1YmplY3Q6
IFJlOiBRdWVyeSBRdWVzdGlvbg0KDQpCaWxsLA0KDQppZiB5b3UgdXNlIGFu IG9yZGVyIGJ5IGNs
YXVzZSBpbiB5b3VyIHF1ZXJ5LCB0aGUgbGltaXQgd2lsbCBwaWNrIHRoZSBm aXJzdA0KMTAwSyBy
b3dzIGluIHRoYXQgb3JkZXIuIFRoYXQgd2F5IHlvdSBjYW4gZW5zdXJlIHRo YXQgYWxsIHJvd3Mg
d2lsbCBiZQ0KcHJvY2Vzc2VkIGluICh3YWl0IGZvciBpdC4uLikgb3JkZXIg OikNCg0KQ2hlZXJz
LA0KDQpXYWx0ZXINCg0KT24gVHVlLCBBdWcgMTgsIDIwMDkgYXQgMTg6NDQs IEJpbGwgQXJidWNr
bGUgPGJpbGxAYXJidWNrbGVsbGMuY29tPiB3cm90ZToNCg0KPiBJIGFtIGlu IG5lZWQgb2Ygc29t
ZSBoZWxwIGZvciB0aGUgZm9sbG93aW5nOg0KPg0KPg0KPg0KPiBTYXkgSSBo YXZlIGEgdGFibGUg
d2l0aCAxTSByb3dzLiAgVXNlcnMgYXJlIGJlaW5nIGFkZGVkIGNvbnN0YW50 bHkgKG5vdA0KPiBk
ZWxldGVkKSBkdXJpbmcgdGhlIHF1ZXJpZXMgdGhhdCBJIGFtIGFib3V0IHRv IGV4cGxhaW4uICBU
aGUgcGsgaXMgdWlkIGFuZA0KPiBhcHBpZC4gIEkgbmVlZCB0byBydW4gcXVl cmllcyBpbiBpbmNy
ZW1lbnRzIG9mIDEwMEsgcm93cyB1bnRpbCByZWFjaGluZyB0aGUNCj4gZW5k IHdpdGhvdXQgZHVw
bGljYXRpbmcgcm93cyBpbiB0aGUgcXVlcmllcy4gIEkgYW0gdXNpbmcgYSBz ZWxlY3Qgc3RhdGVt
ZW50DQo+IHdpdGggYSBsaW1pdCBvZiByb3dfaW5kZXggYW5kIHJvd19jb3Vu dC4gIFRoaXMgc3Rh
cnQgcm93IGlzIHdoZXJlIG15DQo+IHF1ZXN0aW9uIGFyaXNlcy4NCj4NCj4N Cj4NCj4gSWYgSSBt
YWtlIGEgcXVlcnkgd2l0aCBsaW1pdCAwLDEwMDAwMCB0aGVuIDIgbWludXRl cyBsYXRlciAxMDAw
MDAsMTAwMDAwDQo+IHRoZW4gMm1pbnV0ZXMgbGF0ZXIgMzAwMDAwLDEwMDAw MCBhbmQgc28gb24u
ICBNeSBxdWVzdGlvbiBpcyBhcmUgbmV3IHJvd3MNCj4gYWRkZWQgdG8gdGhl IGVuZCBvZiB0aGUg
dGFibGUgb3Igd2lsbCB0aGV5IHJhbmRvbWx5IGFwcGVhciBpbiBteSBxdWVy aWVzPw0KPiBJZiB0
aGV5IGFyZSBhZGRlZCB0byB0aGUgZW5kIG9mIHRoZSB0YWJsZSwgdGhhdCBp cyBmaW5lIGJlY2F1
c2UgSSB3aWxsIHBpY2sNCj4gdGhlbSB1cCBpbiBteSBmaW5hbCBwYXNzLg0K Pg0KPg0KPg0KPiBJ
IGhvcGUgdGhpcyBpcyBjbGVhciBlbm91Z2guICBJZiBub3QsIGxldCBtZSBr bm93IGFuZCBJIHdp
bGwgcHJvdmlkZSBtb3JlDQo+IGluZm9ybWF0aW9uLiAgVGhhbmtzIQ0KPg0K Pg0KDQoNCi0tDQpX
YWx0ZXIgSGVjaywgRW5naW5lZXIgQCBPcGVuIFF1ZXJ5IChodHRwOi8vb3Bl bnF1ZXJ5LmNvbSkN
CkFmZm9yZGFibGUgVHJhaW5pbmcgYW5kIFByb0FjdGl2ZSBTdXBwb3J0IGZv ciBNeVNRTCAmIHJl
bGF0ZWQgdGVjaG5vbG9naWVzDQoNCkZvbGxvdyBvdXIgYmxvZyBhdCBodHRw Oi8vb3BlbnF1ZXJ5
LmNvbS9ibG9nLw0KT3VyRGVsdGE6IGZyZWUgZW5oYW5jZWQgYnVpbGRzIGZv ciBNeVNRTCBAIGh0
dHA6Ly9vdXJkZWx0YS5vcmcNCg0KVGhlIGluZm9ybWF0aW9uIGNvbnRhaW5l ZCBpbiB0aGlzIHRy
YW5zbWlzc2lvbiBtYXkgY29udGFpbiBwcml2aWxlZ2VkIGFuZCBjb25maWRl bnRpYWwgaW5mb3Jt
YXRpb24uIEl0IGlzIGludGVuZGVkIG9ubHkgZm9yIHRoZSB1c2Ugb2YgdGhl IHBlcnNvbihzKSBu
YW1lZCBhYm92ZS4gSWYgeW91IGFyZSBub3QgdGhlIGludGVuZGVkIHJlY2lw aWVudCwgeW91IGFy
ZSBoZXJlYnkgbm90aWZpZWQgdGhhdCBhbnkgcmV2aWV3LCBkaXNzZW1pbmF0 aW9uLCBkaXN0cmli
dXRpb24gb3IgZHVwbGljYXRpb24gb2YgdGhpcyBjb21tdW5pY2F0aW9uIGlz IHN0cmljdGx5IHBy
b2hpYml0ZWQuIElmIHlvdSBhcmUgbm90IHRoZSBpbnRlbmRlZCByZWNpcGll bnQsIHBsZWFzZSBj
b250YWN0IHRoZSBzZW5kZXIgYnkgcmVwbHkgZW1haWwgYW5kIGRlc3Ryb3kg YWxsIGNvcGllcyBv
ZiB0aGUgb3JpZ2luYWwgbWVzc2FnZS4NCg==

Re: Query Question

> To further emphasize this point: A table has no order by itself,

That's not entirely true ;-) Records are stored in some kind of physical
order, some DBMSses implement clustered keys, meaning that the
records are stored ascending order on disk.

However...

>and you should make no assumptions about the order of rows you will get
>back in a select statement, unless you use an ORDER BY clause.

This is correct in that a -result set- does not have an "order" defined
unless you specify an ORDER BY clause.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Query Question

--0016e6d9a2c73eb7bb0471703d33
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

It may be true that "some DBMSs" physically store rows in whatever order you
speicfy; however, this is a MySQL list, and MySQL does not do this (InnoDB
anyway).

For example, take a table with 10,000,000 rows and run a simple select on
it:

Database changed
mysql> SELECT id FROM trans_item LIMIT 1\G
*************************** 1. row ***************************
id: 8919552
1 row in set (0.08 sec)
mysql> SELECT id FROM trans_item ORDER BY id ASC LIMIT 1\G
*************************** 1. row ***************************
id: 8441275
1 row in set (0.08 sec)

Sure, the first query may always return that ID number; however, it may not.



On Tue, Aug 18, 2009 at 2:31 PM, Martijn Tonies wrote:

>
>
> To further emphasize this point: A table has no order by itself,
>>
>
> That's not entirely true ;-) Records are stored in some kind of physical
> order, some DBMSses implement clustered keys, meaning that the
> records are stored ascending order on disk.
>
> However...
>
> and you should make no assumptions about the order of rows you will get
>> back in a select statement, unless you use an ORDER BY clause.
>>
>
> This is correct in that a -result set- does not have an "order" defined
> unless you specify an ORDER BY clause.
>
> With regards,
>
> Martijn Tonies
> Upscene Productions
> http://www.upscene.com
>
> Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
> Anywhere, MySQL, InterBase, NexusDB and Firebird!
>
> Database questions? Check the forum:
> http://www.databasedevelopmentforum.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e6d9a2c73eb7bb0471703d33--

Re: Query Question

> It may be true that "some DBMSs" physically store rows in whatever order
> you
> speicfy;

That's not what I said.

>however, this is a MySQL list, and MySQL does not do this (InnoDB
> anyway).
>
> For example, take a table with 10,000,000 rows and run a simple select on
> it:
>
> Database changed
> mysql> SELECT id FROM trans_item LIMIT 1\G
> *************************** 1. row ***************************
> id: 8919552
> 1 row in set (0.08 sec)
> mysql> SELECT id FROM trans_item ORDER BY id ASC LIMIT 1\G
> *************************** 1. row ***************************
> id: 8441275
> 1 row in set (0.08 sec)
>
> Sure, the first query may always return that ID number; however, it may
> not.

And you're confusing -physical order- (table order) with -result set order-
....

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

Database questions? Check the forum:
http://www.databasedevelopmentforum.com



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org