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
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
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
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
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
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
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
