Re: General Query time question
I'm just guessing here, but I suppose most of the char(1) fields are y/n fields. If that's the case you should consider using a single BIGINT and flag those bits instead. That would considerably reduce the size of the table on one hand and I guess it should improve things speed-wise as well. Obviously, my whole theory stands on a supposition, so I might be wrong in suggesting this. Bogdan "Greer, Darren (MED)" wrote: > id int(11) NOT NULL- Auto Increment > address char(90) NOT NULL > status char(2) NOT NULL > country char(2) > state char(2) > areacode char(3) > ...about 40 char(1) fields. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: General Query time question
Greer, Darren (MED) writes: > If I move the status to another table, and then wanted to get a count of > everyone who is of status 'A', how would that be any quicker? Would I > join the tables? Communication error - reattempting. I meant that if you want to get the count of the number of 'A's often, you could store that number (the count of the number of 'A's) in another table. There'd be no change to your existing table, but there'd be no need to look in it just to get the count. I really have no idea what types of queries you perform or how often, so I'm not really in a position to suggest anything. I just wanted to raise your awareness of the possibility of such a solution. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: General Query time question
My suggestion (using a bigint and flagging the bits instead of using 40 char(1) fields) is only valid if you don't use any of those fields in WHERE clauses in SELECT's. Not that you wouldn't be able to retrieve the entries based on the respective field, but I don't think it would be indeed faster than the way you store the data now. However, I don't know for a fact which method would be faster even when searching, so it's worth a shot. The process is quite simple. Let's say you have only three char(1) fields: alive, married and usresident. We only need three bits for this one, so we'd use a TINYINT (8 bits) - you on the other hand need 40 bits, so you should use a BIGINT (64 bits). Let's call this field "personflags". We'll assign three fixed bits to the three fields we need to implement. Let's say alive is bit 0, married is bit 1 and usresident is bit 2. Once we decided on this, things are quite simple: we start with 0 in a temporary variable; if we find that alive="y" we add 1 to the temp variable; if married="y" we add 10 (binary) and if usresident="y" we add 100 (binary). Then we store the temporary variable in the database as personflags. Now, to test if, say, the entry denotes an US resident, we must find if bit number 2 is set. That would mean retrieving the bigint and testing if ((personflags & 100)!=0) where 100 is obviously a binary value. I wrote the values in binary so it's easier to see what's happening. I guess you're familiar with binary values, so you know that 10(b)=2(d); 100(b)=4(d). For a generic algorithm you should use the following for bit number $bitno in a field with $maxbits used number of bits (in your case, $maxbits=40): - setting the bit: personflags = personbits | (1 << $bitno) That is, we shift an "1" $bitno bits to the left so we get a value of the form "...010...". Then we perform a bitwise OR with the original value. In our example, we would shift left with 2 (usresident is bit 2, remember?) and we'd get 100(b). - testing the bit: if (personflags & (1 << $bitno)) This one's simple - unsetting the bit: personflags = personflags & ((1 << ($maxbits+1)) - 1 - (1<< $bitno))) This one's a little trickier. First we shift a "1" to the left as far as it goes and then one. In our three-values example, this would result in 1000(b). Then we subtract 1. This would result in 111(b). From this we subtract the shifted value which is 100(b). We get 011(b). Now we perform a bitwise AND which results in unsetting bit 2 in personflags. Theoretically, instead of subtracting 1 in order to get 111 we could use bitwise negation (!) but I found that's not always reliable - however, it should be a little faster: personflags = personflags & ((!(1 << ($maxbits+1))) - (1<< $bitno))) HTH Bogdan "Greer, Darren (MED)" wrote: > You are correct, they are simple Y/N fields. I am not familiar with the > process you mentioned, but will do some digging. If you have any > information you could give me that doesn't require too much of your > time, I would appreciate it. > > Thanks, > > Darren - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: General Query time question
If I move the status to another table, and then wanted to get a count of everyone who is of status 'A', how would that be any quicker? Would I join the tables? -Original Message- From: Carl Troein [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 08, 2002 11:34 AM To: [EMAIL PROTECTED] Subject: Re: General Query time question I didn't think/read far enough. One more attempt is due. > > Correction on the query: SELECT count(*) as count FR > OM userdata WHERE > > status = 'A'; I didn't see that it was on that column you had an index, so forget I said 'of course'. Have you used EXPLAIN to figure out if the index is actually used in the query? If you haven't already, consider running an ANALYZE TABLE just so MySQL can get some idea of the distribution for the indexed columns. It might help the optimizer make a better decision if it's not already doing what's best. If the status column only has a few different values, an index will not be used because the overhead of using the index will be greater than the benefit. If this is the case, you'll be better off without the index, and you could consider one of the two options I rambled about in my previous mail. //C - sorry 'bout that. -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: General Query time question
I didn't think/read far enough. One more attempt is due. > > Correction on the query: SELECT count(*) as count FR > OM userdata WHERE > > status = 'A'; I didn't see that it was on that column you had an index, so forget I said 'of course'. Have you used EXPLAIN to figure out if the index is actually used in the query? If you haven't already, consider running an ANALYZE TABLE just so MySQL can get some idea of the distribution for the indexed columns. It might help the optimizer make a better decision if it's not already doing what's best. If the status column only has a few different values, an index will not be used because the overhead of using the index will be greater than the benefit. If this is the case, you'll be better off without the index, and you could consider one of the two options I rambled about in my previous mail. //C - sorry 'bout that. -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: General Query time question
You are correct, they are simple Y/N fields. I am not familiar with the process you mentioned, but will do some digging. If you have any information you could give me that doesn't require too much of your time, I would appreciate it. Thanks, Darren -Original Message- From: Bogdan Stancescu [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 08, 2002 10:37 AM To: Greer, Darren (MED) Cc: '[EMAIL PROTECTED]' Subject: Re: General Query time question I'm just guessing here, but I suppose most of the char(1) fields are y/n fields. If that's the case you should consider using a single BIGINT and flag those bits instead. That would considerably reduce the size of the table on one hand and I guess it should improve things speed-wise as well. Obviously, my whole theory stands on a supposition, so I might be wrong in suggesting this. Bogdan "Greer, Darren (MED)" wrote: > id int(11) NOT NULL- Auto Increment > address char(90) NOT NULL > status char(2) NOT NULL > country char(2) > state char(2) > areacode char(3) > ...about 40 char(1) fields. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: General Query time question
Greer, Darren (MED) writes: > Correction on the query: SELECT count(*) as count FR OM userdata WHERE > status = 'A'; Ah. That does make a difference. MySQL is of course forced to go through all of your data, counting the number of rows with status 'A'. I don't believe an index would do you much good, since it'd add so much to the size of the index file and slow down updates and inserts. Is there any way you could just store that number in another table, maybe? You'd have to update it every time you insert or update the big table, but if that's only done in a few places it should be doable. If that won't work, but it's not important with perfect accuracy, you could run that evil SELECT once a day or however often you like, and cache the result. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: General Query time question
Correction on the query: SELECT count(*) as count FROM userdata WHERE status = 'A'; Sorry. -Original Message- From: Greer, Darren (MED) Sent: Tuesday, January 08, 2002 10:20 AM To: '[EMAIL PROTECTED]' Subject: General Query time question Hello all. I have a database which is around 10million rows. The structure is as follows: id int(11) NOT NULL- Auto Increment address char(90) NOT NULL status char(2) NOT NULL country char(2) state char(2) areacode char(3) ...about 40 char(1) fields. I have an unique index on address, and a non-unique index on address. I also have a non-unique index on status. My questions is this. The following query takes about 7 minutes to run: SELECT count(*) as count from userdata; This is on a PIII 700, with 512MB Ram, running Linux. Is that amount of time normal on a 10million record DB? Or, are the 40 char(1) fields just slowing it down? Thanks for any help, Darren - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
General Query time question
Hello all. I have a database which is around 10million rows. The structure is as follows: id int(11) NOT NULL- Auto Increment address char(90) NOT NULL status char(2) NOT NULL country char(2) state char(2) areacode char(3) ...about 40 char(1) fields. I have an unique index on address, and a non-unique index on address. I also have a non-unique index on status. My questions is this. The following query takes about 7 minutes to run: SELECT count(*) as count from userdata; This is on a PIII 700, with 512MB Ram, running Linux. Is that amount of time normal on a 10million record DB? Or, are the 40 char(1) fields just slowing it down? Thanks for any help, Darren - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php