Mysql server general query log
Hi List, Anybody knows a tool for viewing mysql server general query log in linux. Thanks Ashok -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General Query Log -- No Timestamp
On 6/26/07, Brown, Charles [EMAIL PROTECTED] wrote: The MYSQL general query log does not include timestamp of queries that it logs because queries are logged many many seconds before they are executed. Which version of MySQL are you running? I'm running 5.0.22 on my desktop, but I'm fairly sure that older versions included timestamps in the general query log. I just enabled mine to test this (by adding log = /var/log/mysql.log to /etc/my.cnf) and it looks something like: 070629 8:17:44 6 Connect [EMAIL PROTECTED] on monitoring 6 Query set autocommit=0 6 Query select * from urls where active=y 070629 8:17:45 6 Query INSERT INTO results VALUES (NULL,5,now(),0.5833,35267) 6 Query INSERT INTO results VALUES (NULL,6,now(),0.0137,0) 6 Query INSERT INTO results VALUES (NULL,8,now(),0.7762,28130) 6 Query INSERT INTO results VALUES (NULL,9,now(),0.0348,4217) -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General Query Log -- No Timestamp
The MYSQL general query log does not include timestamp of queries that it logs because queries are logged many many seconds before they are executed. Can someone help me associated queries found in the query log with wall clock? I am trying to get a list of queries that were executed within a given timeslot. I would like to get the description of these queries so that I can give to my webmaster. Based on the description of these queries, he will be able to determine which area the application is causing this on going looping problem that generates 5000 request in 5 mins. Our normal request volume is about 50/min. Can someone help me? Without the timestamp, I wouldn't be able track or identify the queries that came in during the problem time frame. With all things considered, MySQL should be able to append timestamp as it writes these General query records -- I would think. Thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[5] Starting Up General Query Log
Mac OS X 10.4.6 (Tiger), MySQL 5.0.21. Hi folks. I'm needing to start up my general query log to see what's ticking me off. I've looked into safe_mysqld but it's confusing as ... something that's confusing. Anybody know how I can easily turn this thing on for a day, then turn it off? I'm assuming put the following command has to be entered: --log = myqueries.log Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5] Starting Up General Query Log
put log or log=/path/to/file in your config file (my.cnf) and restart the server. To turn it off you have to take it out of the my.cnf and restart the server. I've put in a request to make the general log something that can be dynamically turned on. -Sheeri On 5/19/06, Rich [EMAIL PROTECTED] wrote: Mac OS X 10.4.6 (Tiger), MySQL 5.0.21. Hi folks. I'm needing to start up my general query log to see what's ticking me off. I've looked into safe_mysqld but it's confusing as ... something that's confusing. Anybody know how I can easily turn this thing on for a day, then turn it off? I'm assuming put the following command has to be entered: --log = myqueries.log Cheers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5] Starting Up General Query Log
Yes. idea #1 -- reply all, including the list. idea #2 -- what's in the error logs? Check that the user that runs mysql has permission to write to the file and that /var/log exists. -Sheeri On 5/19/06, Rich [EMAIL PROTECTED] wrote: Hi there. I added the my.cnf file (it wasn't there) and put this into it: log=/var/log/myrequests.log Now MySQL won't start. Any ideas? Cheers On May 19, 2006, at 11:35 AM, sheeri kritzer wrote: put log or log=/path/to/file in your config file (my.cnf) and restart the server. To turn it off you have to take it out of the my.cnf and restart the server. Rich Fortnum [EMAIL PROTECTED] Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5] Starting Up General Query Log
Why this list goes private I'll never know. I guess that's why I always get two copies. In the errors log: Found option without preceding group in config file: /etc/my.cnf at line: 1 Fatal error in defaults handling. Program aborted /var/log/ does indeed exist root runs mysqld On May 19, 2006, at 12:01 PM, sheeri kritzer wrote: Yes. idea #1 -- reply all, including the list. idea #2 -- what's in the error logs? Check that the user that runs mysql has permission to write to the file and that /var/log exists. Rich Fortnum [EMAIL PROTECTED] Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [5] Starting Up General Query Log
That means that your options have no group. Options should go under the program they're intended to be run under, for instance [mysqldump] user=root [mysql.client] user=guest [mysqld] log=/path/to/logfile You want the mysqld program (mysql server) to use the general log, so put it under a section marked [mysqld] in your config file. -Sheeri On 5/19/06, Rich [EMAIL PROTECTED] wrote: Why this list goes private I'll never know. I guess that's why I always get two copies. In the errors log: Found option without preceding group in config file: /etc/my.cnf at line: 1 Fatal error in defaults handling. Program aborted /var/log/ does indeed exist root runs mysqld On May 19, 2006, at 12:01 PM, sheeri kritzer wrote: Yes. idea #1 -- reply all, including the list. idea #2 -- what's in the error logs? Check that the user that runs mysql has permission to write to the file and that /var/log exists. Rich Fortnum [EMAIL PROTECTED] Toronto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Named Pipe for General Query Log
Hi Jake, all, please note that the following remarks are about communicating via named pipes in general, not specific for MySQL. I do _not_ comment whether the idea is good or bad, will work, alternatives, ... Jake Peavy wrote: Hey, I sent this a while ago, but never received a response. This still seems to exist under 5.0.15-standard (at least under mysql-standard-5.0.15-linux-i686-glibc23) Can anyone from MySQL comment on this or should I open it as a bug? Thanks, JP On 6/11/05, Jake Peavy [EMAIL PROTECTED] wrote: Has anyone been able to use a named pipe for their general query log (or any of the other logfiles for that matter)? I tried the following as user mysql: rm /var/lib/mysql/myhost.log mkfifo -m 0660 /var/lib/mysql/myhost.log but the mysql server would not start. Sure: Works as designed. man 2 open will tell you that an open() call on a named pipe synchronizes: Any such call will block until there is a corresponding call at the other end of the pipe. So the general technique is: mkfifo the_pipe reader_command the_pipe writer_command the_pipe Note that the pipe has a limited buffer capacity, so the writer cannot produce more info than the reader has processed: If your reader is slow (say, more and a human watching), the writer has to wait. Also, writing to the pipe fails if there is no reader attached. So if your reader terminates (crash, q input to more, ...), your writer cannot write any more, this may be fatal (depends on error handling). I think it would be very useful to be able to use a FIFO for this so I can use the log for debugging/info without having to create a log rotation script. For any log of a MySQL server, this is IMHO useful _only_ in a test environment, because of the speed and stability restrictions described above. I am running 5.0.2-alpha-standard on linux on i386. These pipe semantics hold for any Unix since pipes were introduced: For anonymous pipes (open() implicit in pipe()), since the early 1970s; for named pipes (aka FIFOs), since ATT Unix System V in the late 1980s (AFAIR). HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Named Pipe for General Query Log
Hey, I sent this a while ago, but never received a response. This still seems to exist under 5.0.15-standard (at least under mysql-standard-5.0.15-linux-i686-glibc23) Can anyone from MySQL comment on this or should I open it as a bug? Thanks, JP On 6/11/05, Jake Peavy [EMAIL PROTECTED] wrote: Has anyone been able to use a named pipe for their general query log (or any of the other logfiles for that matter)? I tried the following as user mysql: rm /var/lib/mysql/myhost.log mkfifo -m 0660 /var/lib/mysql/myhost.log but the mysql server would not start. I think it would be very useful to be able to use a FIFO for this so I can use the log for debugging/info without having to create a log rotation script. I am running 5.0.2-alpha-standard on linux on i386. Thanks. F
Named Pipe for General Query Log
Has anyone been able to use a named pipe for their general query log (or any of the other logfiles for that matter)? I tried the following as user mysql: rm /var/lib/mysql/myhost.log mkfifo -m 0660 /var/lib/mysql/myhost.log but the mysql server would not start. I think it would be very useful to be able to use a FIFO for this so I can use the log for debugging/info without having to create a log rotation script. I am running 5.0.2-alpha-standard on linux on i386. Thanks. F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General query question
Hi, Mysql 4.0.14 In a seconrio, some reocrds are missing from a child table. If we run this query it returns the missing records: select a.field1, b.field2 from table1 a left join table2 b on (a.field1 = b.field1) where b.field1 is null I want to create entries in the child table (table2) for the missing records. In table2 the primary key is of type Integer, for each new entry it should be Max(table2.PrimaryKeyfield) + 1. How can I do this in one sql command (or is it possilbe at all)? something like: Insert into table2 (primarykeyfiled, field1, field2) Max(table2.priamrykeyfield) + 1 select a.field1, a.field2 from table1 a left join table2 b on (a.field1 = b.field1) where b.field1 is null regards ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General query question
I want to create entries in the child table (table2) for the missing records. In table2 the primary key is of type Integer, for each new entry it should be Max(table2.PrimaryKeyfield) + 1. Why not make the primary key in table2 autoincrement? If you have an autoincrement field as primary key MySQL will do the max(..)+1 automatically for you! A query like Insert into table2 (field1, field2) select a.field1, a.field2 from table1 a left join table2 b on (a.field1 = b.field1) where b.field1 is null could then be used to fill up the missing records in table2... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General query question
Thanks, I did think of it but not having the option as this is linked to executables, which I'm sure have some sorts of calculation for this field to calculate the next value. regards --- Jigal van Hemert [EMAIL PROTECTED] wrote: I want to create entries in the child table (table2) for the missing records. In table2 the primary key is of type Integer, for each new entry it should be Max(table2.PrimaryKeyfield) + 1. Why not make the primary key in table2 autoincrement? If you have an autoincrement field as primary key MySQL will do the max(..)+1 automatically for you! A query like Insert into table2 (field1, field2) select a.field1, a.field2 from table1 a left join table2 b on (a.field1 = b.field1) where b.field1 is null could then be used to fill up the missing records in table2... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query execution times in general query log?
I have a need to find out how long each of the queries executed against a mysql server are taking. I found this post, which indicates that this information can be found in the general query log: http://www.phpbuilder.com/mail/php-general/2002122/0876.php The manual note about it says the binary update log includes execution times for writes only, and if you want timing info for general queries, to use the general query log. However, the output in my general query log (v3.23.54) only has a timestamp, not an execution time. So, a few questions... 1) Has this changed in 4.0? 2) Are there options to get this info put in the log (in 3.23 or otherwise)? 3) Does anybody have a better way to get this info from a running instance (not necessarily in realtime)? -- - Adam - Adam Fields, Managing Partner, [EMAIL PROTECTED] Surgam, Inc. is a technology consulting firm with strong background in delivering scalable and robust enterprise web and IT applications. http://www.adamfields.com - 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: Query execution times in general query log?
At 16:52 -0500 2/26/03, 1LT John W. Holmes wrote: I have a need to find out how long each of the queries executed against a mysql server are taking. Why can't there just be a function that returns this? I mean, it's already printed to the screen when you're running from the command line. Hopefully I've missed something and it's already there, but if not, please add it to the feature request. There can't be a function that returns this because execution time such as you're seeing is measured on the client side and the query is executed on the server side. If you mean, why can't there be a SQL function to return this, well, that's kind of like your eye trying to see itself. :-) - 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
analyzing mysql general query log
Hello, Can anyone recommend a script to analyze a mysql server general query log. My goal is to determine which users are putting the most load on the server. Any other methods to achieve this same goal would be appreciated as well. Thank you. _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail - 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 log
MySQL gurus: I'm a big fan of the general query log for taking the occasional audit of activity against our database server, as well as a tool for checking on the efficiency of new (mostly PHP) projects that interact with MySQL. What I don't like is that I have to stop and restart the server process twice (once to turn the log on and once to turn it back off) in order to get the output. I've read through the documentation at mysql.com and would have searched through the list archives if I wasn't getting document contains no data messages from lists.mysql.com at the moment. We use mytop (http://jeremy.zawodny.com/mysql/mytop/) to keep some track of the queries that are being run, but the output pales in comparison (for this use) to that from the general query log. (1) Is there a way to turn logging on for brief periods (no more than 15 minutes) without having to stop and restart? (2) Is there some way to get similar output with a different tool? [mysql query] TIA, Andy Andy Ingham Systems Librarian Academic Affairs Library UNC-Chapel Hill 919-962-1288 [EMAIL PROTECTED] - 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 log option in my.cnf?
Is there a way of turning on the general query log in the my.cnf file or do I have to edit the init-script? Personally, I would very much like to have only one place to set all the configuration options (preferrably my.cnf). Setting datadir and other options both in the my.cnf file and the init-script seems to me like a great way of obfuscating the system setup. -- - håkon - 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 log option in my.cnf?
Håkon Eriksen wrote: Is there a way of turning on the general query log in the my.cnf file or do I have to edit the init-script? Yes, all command-line options can be specified in my.cnf. Read the following page for more info: http://www.mysql.com/doc/O/p/Option_files.html -myk - 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
Hi Guru's, We say MySQL is open source database, so it can easily be adjusted to your requirements. But I wonder how many administrators have actually gone into the source code and made any modifications to match their specific needs, if any. If no, then why is MySQL better than other databases available in market, if we leave the cost alone. I do not intend to challenge MySQL, but just wanted to know how this elite group can help someone if such a need arises !! Has anyone other than the core development team tried any changes before ?? With regards, Paras - 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
Hi. On Mon, Apr 29, 2002 at 07:58:57AM -0500, [EMAIL PROTECTED] wrote: Hi Guru's, We say MySQL is open source database, so it can easily be adjusted to your requirements. But I wonder how many administrators have actually gone into the source code and made any modifications to match their specific needs, if any. Have a look at http://www.mysql.com/doc/C/o/Contributors.html (and ignore the thanks because we could use existing stuff entries). And the other point is: It is not relevant if you yourself would change it: you can hire someone in the know in order to change it for you. The advantage is that you can do with it what you want, even if MySQL AB would join the Dark Side sometime. If no, then why is MySQL better than other databases available in market, if we leave the cost alone. If no, it could also simply mean, that MySQL has an excellent development team and nobody needed to change things. In other words, your criteria didn't fit your conclusion. I do not intend to challenge MySQL, but just wanted to know how this elite group can help someone if such a need arises!! Which elite group? And changing yourself is something different than changing for others. Has anyone other than the core development team tried any changes before ?? See above. The answer to your question is: Yes. With regards, Paras Bye, Benjamin. -- [EMAIL PROTECTED] - 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
Hi We say MySQL is open source database, so it can easily be adjusted to your requirements. But I wonder how many administrators have actually gone into the source code and made any modifications to match their specific needs, if any. The point is you can make modifications if you want to, try making modifications to Microsoft SQL server source code, and because you have the source code you can compile the software to suit your own needs, try doing that with a commercial database product. If no, then why is MySQL better than other databases available in market, if we leave the cost alone. Commercial databases get updated by a limited number of programmers once per year if your lucky, opensource projects get updated many times per year by an unlimited about of programmers. If you find a bug in MySql you have the choice to fix the bug yourself and have your bug fix included in the release software. If you find a bug in say Microsoft SQL server all you can do is report the bug to Microsoft and hope they fix it, then to get a copy of the bug fixed software you will have to buy a new version of the software. I do not intend to challenge MySQL, but just wanted to know how this elite group can help someone if such a need arises !! This is not an elite group, its just normal people helping each other out, if you have a problem people will try to help you if they can, opensource is all about community sprit and not just about dollars in pockets. Peter Kelly - A Happy MySql User. http://www.TrafficG.com - Original Message - From: Paras Mukadam [EMAIL PROTECTED] To: MySQL Sent: Monday, April 29, 2002 1:58 PM Subject: general query Hi Guru's, We say MySQL is open source database, so it can easily be adjusted to your requirements. But I wonder how many administrators have actually gone into the source code and made any modifications to match their specific needs, if any. If no, then why is MySQL better than other databases available in market, if we leave the cost alone. I do not intend to challenge MySQL, but just wanted to know how this elite group can help someone if such a need arises !! Has anyone other than the core development team tried any changes before ?? With regards, Paras - 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
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
