How to tweek the max connections
Hi all! I would like know if there are any calculatios for specifying the maximum number of the 'max_connections' value. I understand that max_connections should be decided by the remaining RAM size of the system. But exactly how should it be actually calculated? I am currently using RHEL 5.3 32 bit and following are samples of the memory usage of my system. [root@GWM bin]# ps -eo pcpu,vsz,rss,args | grep mysql 0.0 5420 1172 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/GWM.pid 0.0 332592 40040 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/lib/mysql/GWM.err --pid-file=/var/lib/mysql/GWM.pid -- socket=/var/lib/mysql/mysql.sock --port=3306 0.0 4980 760 grep mysql [root@GWM bin]# free total used free sharedbuffers cached Mem: 16623956 127270123896944 0 103668 11634904 -/+ buffers/cache: 988440 15635516 Swap: 10482404 1368 10481036 Version of mysql is , [root@GWM bin]# mysqladmin -u root --password=groundwork variables | grep version | version | 5.5.25-log | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | i686 | | version_compile_os| Linux Thanks, Yu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to change max simultaneous connection parameter in mysql.
2013/3/24 Reindl Harald h.rei...@thelounge.net Am 24.03.2013 05:20, schrieb spameden: 2013/3/19 Rick James rja...@yahoo-inc.com: you never have hosted a large site Check my email address before saying that. :D as said, big company does not have only geniusses I do not judge only on 1 parameter, Rick has been constantly helping here and I'm pretty sure he has more knowledge on MySQL than you. 20 may be low, but 100 is rather high. Never use apache2 it has so many problems under load.. if you are too supid to configure it yes Ever heard about Slow HTTP DoS attack? The best combo is php5-fpm+nginx. Handles loads of users at once if well tuned Apache 2.4 handles the load of 600 parallel executed php-scripts from our own CMS-system Nginx serves static content way better than apache2 (did few benchmarks already). nginx+php5-fpm handles better load than apache2-prefork+mod_php you can google benchmarks if you dont trust me also nginx eats much less memory than apache2 php5-fpm can be tuned as well to suit your needs if you have lots of dynamic content maybe you guys should learn what a opcode-cache is and how to compile and optimize software (binaries and config) o'rly?
Re: How to change max simultaneous connection parameter in mysql.
Am 02.04.2013 16:09, schrieb spameden: 2013/3/24 Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net Am 24.03.2013 05:20, schrieb spameden: 2013/3/19 Rick James rja...@yahoo-inc.com mailto:rja...@yahoo-inc.com: you never have hosted a large site Check my email address before saying that. :D as said, big company does not have only geniusses I do not judge only on 1 parameter, Rick has been constantly helping here and I'm pretty sure he has more knowledge on MySQL than you. but the MySQL knowledge alone is not enough in context of a webserver not to say irrelevant 20 may be low, but 100 is rather high. Never use apache2 it has so many problems under load.. if you are too supid to configure it yes Ever heard about Slow HTTP DoS attack? my config says yes as i heard about many things because it is my daily job 0 0 LOGtcp -- eth0 * !local-network/24 0.0.0.0/0multiport dports 80,443 tcpflags: 0x17/0x02 #conn src/32 50 limit: avg 100/hour burst 5 LOG flags 0 level 7 prefix Firewall Slowloris: 0 0 DROP tcp -- eth0 * !local-network/24 0.0.0.0/0multiport dports 80,443 tcpflags: 0x17/0x02 #conn src/32 50 signature.asc Description: OpenPGP digital signature
Re: How to change max simultaneous connection parameter in mysql.
Am 02.04.2013 22:56, schrieb Rick James: I hear that nginx is very fast for a certain class of web serving. yes But what happens if a web page needs to do a SELECT? what should happen? Is nginx single-threaded, thereby sitting idle waiting for the SELECT? why should it do that? And, should you run 8 nginx web servers on an 8-core box? why should you do that? http://en.wikipedia.org/wiki/Nginx nginx uses an asynchronous event-driven approach to handling requests -Original Message- From: spameden [mailto:spame...@gmail.com] Sent: Tuesday, April 02, 2013 7:10 AM To: Reindl Harald Cc: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. 2013/3/24 Reindl Harald h.rei...@thelounge.net Am 24.03.2013 05:20, schrieb spameden: 2013/3/19 Rick James rja...@yahoo-inc.com: you never have hosted a large site Check my email address before saying that. :D as said, big company does not have only geniusses I do not judge only on 1 parameter, Rick has been constantly helping here and I'm pretty sure he has more knowledge on MySQL than you. 20 may be low, but 100 is rather high. Never use apache2 it has so many problems under load.. if you are too supid to configure it yes Ever heard about Slow HTTP DoS attack? The best combo is php5-fpm+nginx. Handles loads of users at once if well tuned Apache 2.4 handles the load of 600 parallel executed php-scripts from our own CMS-system Nginx serves static content way better than apache2 (did few benchmarks already). nginx+php5-fpm handles better load than apache2-prefork+mod_php you can google benchmarks if you dont trust me also nginx eats much less memory than apache2 php5-fpm can be tuned as well to suit your needs if you have lots of dynamic content maybe you guys should learn what a opcode-cache is and how to compile and optimize software (binaries and config) o'rly? signature.asc Description: OpenPGP digital signature
RE: How to change max simultaneous connection parameter in mysql.
(Thanks for the comment, spameden.) Well, I was going to drop the thread, but he baited me. I _do_ know something about web serving... Should I recount the number of times I have traced a database meltdown back to MaxClients being too big? They are _ugly_ meltdowns -- hundreds of point-queries stumbling over themselves, flooding the slowlog with queries that should never take more than milliseconds. More and more db requests come in, non finishing, thereby stalling the web server threads, etc. Another point to make -- once a web server (Apache or...) has saturated the CPU (or other shared resource), there is really no advantage, only disadvantage, in starting more web pages. The will simply contend for the saturated resource, thereby slowing down _all_ threads. It is better (at this point) to queue up (or drop) further requests, thereby giving the CPU a chance to actually finish something. Yet another point... If [ SUM(MaxClients) over the web servers you have ] [ SUM(max_connections) over the Slaves ], then you are threatening to have mysql refuse connections; this probably leads to broken web pages, maybe even 404s or 500s. Granted, you have (at least) 3 choices: decrease MaxClients, increase max_connections, or add more Slaves. If mysql has most of max_connections _actively_ running querieds, then it is probably stumbling badly, so I vote against increasing that. Adding a Slave cannot be done 'instantly'. That leaves decreasing MaxClients, which is quick and easy. Furthermore, the SE (in one of the meltdowns) killed Apache; this led to a prompt clear up of all the issues -- poor web response, mysql melting down, etc. Sometimes less is better! -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Tuesday, April 02, 2013 8:29 AM To: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. Am 02.04.2013 16:09, schrieb spameden: 2013/3/24 Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net Am 24.03.2013 05:20, schrieb spameden: 2013/3/19 Rick James rja...@yahoo-inc.com mailto:rja...@yahoo-inc.com: you never have hosted a large site Check my email address before saying that. :D as said, big company does not have only geniusses I do not judge only on 1 parameter, Rick has been constantly helping here and I'm pretty sure he has more knowledge on MySQL than you. but the MySQL knowledge alone is not enough in context of a webserver not to say irrelevant 20 may be low, but 100 is rather high. Never use apache2 it has so many problems under load.. if you are too supid to configure it yes Ever heard about Slow HTTP DoS attack? my config says yes as i heard about many things because it is my daily job 0 0 LOGtcp -- eth0 * !local-network/24 0.0.0.0/0multiport dports 80,443 tcpflags: 0x17/0x02 #conn src/32 50 limit: avg 100/hour burst 5 LOG flags 0 level 7 prefix Firewall Slowloris: 0 0 DROP tcp -- eth0 * !local-network/24 0.0.0.0/0multiport dports 80,443 tcpflags: 0x17/0x02 #conn src/32 50 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to change max simultaneous connection parameter in mysql.
Am 02.04.2013 23:15, schrieb Rick James: SELECT is not performed in the same thread as nginx; it is performed in another process, or even (in big web setups) in a different host. Therefore, nginx would be in some form of wait state, thereby not really using the CPU. tell me something new -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Tuesday, April 02, 2013 2:00 PM To: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. Am 02.04.2013 22:56, schrieb Rick James: I hear that nginx is very fast for a certain class of web serving. yes But what happens if a web page needs to do a SELECT? what should happen? Is nginx single-threaded, thereby sitting idle waiting for the SELECT? why should it do that? And, should you run 8 nginx web servers on an 8-core box? why should you do that? http://en.wikipedia.org/wiki/Nginx nginx uses an asynchronous event-driven approach to handling requests -Original Message- From: spameden [mailto:spame...@gmail.com] Sent: Tuesday, April 02, 2013 7:10 AM To: Reindl Harald Cc: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. 2013/3/24 Reindl Harald h.rei...@thelounge.net Am 24.03.2013 05:20, schrieb spameden: 2013/3/19 Rick James rja...@yahoo-inc.com: you never have hosted a large site Check my email address before saying that. :D as said, big company does not have only geniusses I do not judge only on 1 parameter, Rick has been constantly helping here and I'm pretty sure he has more knowledge on MySQL than you. 20 may be low, but 100 is rather high. Never use apache2 it has so many problems under load.. if you are too supid to configure it yes Ever heard about Slow HTTP DoS attack? The best combo is php5-fpm+nginx. Handles loads of users at once if well tuned Apache 2.4 handles the load of 600 parallel executed php-scripts from our own CMS-system Nginx serves static content way better than apache2 (did few benchmarks already). nginx+php5-fpm handles better load than apache2-prefork+mod_php you can google benchmarks if you dont trust me also nginx eats much less memory than apache2 php5-fpm can be tuned as well to suit your needs if you have lots of dynamic content maybe you guys should learn what a opcode-cache is and how to compile and optimize software (binaries and config) o'rly? signature.asc Description: OpenPGP digital signature
RE: How to change max simultaneous connection parameter in mysql.
SELECT is not performed in the same thread as nginx; it is performed in another process, or even (in big web setups) in a different host. Therefore, nginx would be in some form of wait state, thereby not really using the CPU. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Tuesday, April 02, 2013 2:00 PM To: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. Am 02.04.2013 22:56, schrieb Rick James: I hear that nginx is very fast for a certain class of web serving. yes But what happens if a web page needs to do a SELECT? what should happen? Is nginx single-threaded, thereby sitting idle waiting for the SELECT? why should it do that? And, should you run 8 nginx web servers on an 8-core box? why should you do that? http://en.wikipedia.org/wiki/Nginx nginx uses an asynchronous event-driven approach to handling requests -Original Message- From: spameden [mailto:spame...@gmail.com] Sent: Tuesday, April 02, 2013 7:10 AM To: Reindl Harald Cc: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. 2013/3/24 Reindl Harald h.rei...@thelounge.net Am 24.03.2013 05:20, schrieb spameden: 2013/3/19 Rick James rja...@yahoo-inc.com: you never have hosted a large site Check my email address before saying that. :D as said, big company does not have only geniusses I do not judge only on 1 parameter, Rick has been constantly helping here and I'm pretty sure he has more knowledge on MySQL than you. 20 may be low, but 100 is rather high. Never use apache2 it has so many problems under load.. if you are too supid to configure it yes Ever heard about Slow HTTP DoS attack? The best combo is php5-fpm+nginx. Handles loads of users at once if well tuned Apache 2.4 handles the load of 600 parallel executed php-scripts from our own CMS-system Nginx serves static content way better than apache2 (did few benchmarks already). nginx+php5-fpm handles better load than apache2-prefork+mod_php you can google benchmarks if you dont trust me also nginx eats much less memory than apache2 php5-fpm can be tuned as well to suit your needs if you have lots of dynamic content maybe you guys should learn what a opcode-cache is and how to compile and optimize software (binaries and config) o'rly? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How to change max simultaneous connection parameter in mysql.
I hear that nginx is very fast for a certain class of web serving. But what happens if a web page needs to do a SELECT? Is nginx single-threaded, thereby sitting idle waiting for the SELECT? And, should you run 8 nginx web servers on an 8-core box? -Original Message- From: spameden [mailto:spame...@gmail.com] Sent: Tuesday, April 02, 2013 7:10 AM To: Reindl Harald Cc: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. 2013/3/24 Reindl Harald h.rei...@thelounge.net Am 24.03.2013 05:20, schrieb spameden: 2013/3/19 Rick James rja...@yahoo-inc.com: you never have hosted a large site Check my email address before saying that. :D as said, big company does not have only geniusses I do not judge only on 1 parameter, Rick has been constantly helping here and I'm pretty sure he has more knowledge on MySQL than you. 20 may be low, but 100 is rather high. Never use apache2 it has so many problems under load.. if you are too supid to configure it yes Ever heard about Slow HTTP DoS attack? The best combo is php5-fpm+nginx. Handles loads of users at once if well tuned Apache 2.4 handles the load of 600 parallel executed php-scripts from our own CMS-system Nginx serves static content way better than apache2 (did few benchmarks already). nginx+php5-fpm handles better load than apache2-prefork+mod_php you can google benchmarks if you dont trust me also nginx eats much less memory than apache2 php5-fpm can be tuned as well to suit your needs if you have lots of dynamic content maybe you guys should learn what a opcode-cache is and how to compile and optimize software (binaries and config) o'rly? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to change max simultaneous connection parameter in mysql.
2013/4/3 Rick James rja...@yahoo-inc.com SELECT is not performed in the same thread as nginx; it is performed in another process, or even (in big web setups) in a different host. Therefore, nginx would be in some form of wait state, thereby not really using the CPU. ofc select is not performed in nginx thread, nginx acts as a proxying server and just passes the request to the backend it's entirely depends on your backend how fast it's gonna process certain SELECT and ofc depends on what kind of database you've got if backend takes too long to respond nginx just shows 502 error with timeout from backend (by default: 30s). good practice is to have multiple backends behind load balancer, so under huge load no single request would be lost. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Tuesday, April 02, 2013 2:00 PM To: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. Am 02.04.2013 22:56, schrieb Rick James: I hear that nginx is very fast for a certain class of web serving. yes But what happens if a web page needs to do a SELECT? what should happen? Is nginx single-threaded, thereby sitting idle waiting for the SELECT? nginx is multi threaded and it supports SMP architecture, there is main process which controls everything and nginx configuration can be reloaded with zero downtime. I saw multiple test where under load (simple DDoS simulation attack like there where 40k bots hitting the site at once) nginx+php5-fpm dropped much less requests than apache2 + mod_php. apache2 is so bad at eating memory and system resources. why should it do that? And, should you run 8 nginx web servers on an 8-core box? no, you just tune worker_processes 8; why should you do that? http://en.wikipedia.org/wiki/Nginx nginx uses an asynchronous event-driven approach to handling requests -Original Message- From: spameden [mailto:spame...@gmail.com] Sent: Tuesday, April 02, 2013 7:10 AM To: Reindl Harald Cc: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. 2013/3/24 Reindl Harald h.rei...@thelounge.net Am 24.03.2013 05:20, schrieb spameden: 2013/3/19 Rick James rja...@yahoo-inc.com: you never have hosted a large site Check my email address before saying that. :D as said, big company does not have only geniusses I do not judge only on 1 parameter, Rick has been constantly helping here and I'm pretty sure he has more knowledge on MySQL than you. 20 may be low, but 100 is rather high. Never use apache2 it has so many problems under load.. if you are too supid to configure it yes Ever heard about Slow HTTP DoS attack? The best combo is php5-fpm+nginx. Handles loads of users at once if well tuned Apache 2.4 handles the load of 600 parallel executed php-scripts from our own CMS-system Nginx serves static content way better than apache2 (did few benchmarks already). nginx+php5-fpm handles better load than apache2-prefork+mod_php you can google benchmarks if you dont trust me also nginx eats much less memory than apache2 php5-fpm can be tuned as well to suit your needs if you have lots of dynamic content maybe you guys should learn what a opcode-cache is and how to compile and optimize software (binaries and config) o'rly? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to change max simultaneous connection parameter in mysql.
Am 02.04.2013 23:14, schrieb Rick James: (Thanks for the comment, spameden.) Well, I was going to drop the thread, but he baited me. I _do_ know something about web serving... maybe Should I recount the number of times I have traced a database meltdown back to MaxClients being too big? They are _ugly_ meltdowns -- hundreds of point-queries stumbling over themselves, flooding the slowlog with queries that should never take more than milliseconds. More and more db requests come in, non finishing, thereby stalling the web server threads, etc. been there, done that it is a matter of the application design to avoid deadlocks in such cases Another point to make -- once a web server (Apache or...) has saturated the CPU (or other shared resource), there is really no advantage, only disadvantage, in starting more web pages. The will simply contend for the saturated resource, thereby slowing down _all_ threads. It is better (at this point) to queue up (or drop) further requests, thereby giving the CPU a chance to actually finish something. but with 20-100 requests a webserver these days is NOT saturated MaxClients 20 is laughable i had a high trafic site with MaxClients set to 500, driven with our CMS-system teh CPU load was at 80% and the page got damned slow because you have to wait before your images could be loaded after raise MacClients to 600 it ran smooth, the CPU was around 85% again: 20-100 MaxClients is laughable and only suiteable for a dedicated, low-powered machine hosting only one domain Yet another point... If [ SUM(MaxClients) over the web servers you have ] [ SUM(max_connections) over the Slaves ], then you are threatening to have mysql refuse connections no, my db-layer is traing again for some times and the chance to get a free slot because other workers are serving images is proven in practice damned high -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Tuesday, April 02, 2013 8:29 AM To: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. Am 02.04.2013 16:09, schrieb spameden: 2013/3/24 Reindl Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net Am 24.03.2013 05:20, schrieb spameden: 2013/3/19 Rick James rja...@yahoo-inc.com mailto:rja...@yahoo-inc.com: you never have hosted a large site Check my email address before saying that. :D as said, big company does not have only geniusses I do not judge only on 1 parameter, Rick has been constantly helping here and I'm pretty sure he has more knowledge on MySQL than you. but the MySQL knowledge alone is not enough in context of a webserver not to say irrelevant 20 may be low, but 100 is rather high. Never use apache2 it has so many problems under load.. if you are too supid to configure it yes Ever heard about Slow HTTP DoS attack? my config says yes as i heard about many things because it is my daily job 0 0 LOGtcp -- eth0 * !local-network/24 0.0.0.0/0multiport dports 80,443 tcpflags: 0x17/0x02 #conn src/32 50 limit: avg 100/hour burst 5 LOG flags 0 level 7 prefix Firewall Slowloris: 0 0 DROP tcp -- eth0 * !local-network/24 0.0.0.0/0multiport dports 80,443 tcpflags: 0x17/0x02 #conn src/32 50 signature.asc Description: OpenPGP digital signature
Re: How to change max simultaneous connection parameter in mysql.
Am 24.03.2013 05:20, schrieb spameden: 2013/3/19 Rick James rja...@yahoo-inc.com: you never have hosted a large site Check my email address before saying that. :D as said, big company does not have only geniusses 20 may be low, but 100 is rather high. Never use apache2 it has so many problems under load.. if you are too supid to configure it yes The best combo is php5-fpm+nginx. Handles loads of users at once if well tuned Apache 2.4 handles the load of 600 parallel executed php-scripts from our own CMS-system maybe you guys should learn what a opcode-cache is and how to compile and optimize software (binaries and config) signature.asc Description: OpenPGP digital signature
Re: How to change max simultaneous connection parameter in mysql.
2013/3/24 Reindl Harald h.rei...@thelounge.net Am 24.03.2013 05:20, schrieb spameden: 2013/3/19 Rick James rja...@yahoo-inc.com: you never have hosted a large site Check my email address before saying that. :D as said, big company does not have only geniusses 20 may be low, but 100 is rather high. Never use apache2 it has so many problems under load.. if you are too supid to configure it yes The best combo is php5-fpm+nginx. Handles loads of users at once if well tuned Apache 2.4 handles the load of 600 parallel executed php-scripts from our own CMS-system maybe you guys should learn what a opcode-cache is and how to compile and optimize software (binaries and config) And maybe you should learn some good manners.
Re: How to change max simultaneous connection parameter in mysql.
2013/3/19 Rick James rja...@yahoo-inc.com: you never have hosted a large site Check my email address before saying that. :D 20 may be low, but 100 is rather high. Never use apache2 it has so many problems under load.. The best combo is php5-fpm+nginx. Handles loads of users at once if well tuned. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Monday, March 18, 2013 1:36 PM To: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. Am 18.03.2013 21:01, schrieb Rick James: 20 is plenty if your pages run fast enough it is not you never have hosted a large site Excess clients after MaxClients are queued in Apache so what - it doe snot help you been there, done that if you have some hundret USERS at the same time any every of them is requesting the same page with a lot of images you are simply DEAD with a limit of 20 in your configuration If the 20 are consuming resources (eg cpu/disk) it is better to queue the excess than to have everybody stumbling over each other. if your server can not serve more than 20 simultaionous requests you are not doing any serious things sorry, 20 can be done with any crappy notebook these days In MySQL, the excess clients beyond max_connections are give an error. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Monday, March 18, 2013 12:15 PM To: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. Am 18.03.2013 19:26, schrieb Rick James: If you are running Apache with MaxClients set too high, that can cause the problem. too high is relative That Apache setting should be something like 20. (Other web servers have similar settings.) 20 is a laughable value as long you are not hosting only sites with no users at all i have seen MaxClients 500 be critical while the hardware was not overloaded and we had THOUSANDS of users which liked to get the website with all it's images, no way with stupid settings of 20 which means only ONE USER at the same time can fetch a single page with images and stylesheets -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to change max simultaneous connection parameter in mysql.
Am 19.03.2013 01:18, schrieb Rick James: you never have hosted a large site Check my email address before saying that. yahoo - so what this is a large enough copmany that i am still sure you never was allowed to touch a webserver for production 20 may be low, but 100 is rather high you do relly not understand it * i had a project with a large amoutn of parallel users * the MaxClients was up to 400 * the page-loading was UNACCEPTABLE slow * after raise up the limit to 600 it got acceptable * yes the hardware was able to satisfy the requests * yes you need a LOT of memory and CPU so please get rid of your i386 and stop to explain me the world -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Monday, March 18, 2013 1:36 PM To: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. Am 18.03.2013 21:01, schrieb Rick James: 20 is plenty if your pages run fast enough it is not you never have hosted a large site Excess clients after MaxClients are queued in Apache so what - it doe snot help you been there, done that if you have some hundret USERS at the same time any every of them is requesting the same page with a lot of images you are simply DEAD with a limit of 20 in your configuration If the 20 are consuming resources (eg cpu/disk) it is better to queue the excess than to have everybody stumbling over each other. if your server can not serve more than 20 simultaionous requests you are not doing any serious things sorry, 20 can be done with any crappy notebook these days In MySQL, the excess clients beyond max_connections are give an error. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Monday, March 18, 2013 12:15 PM To: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. Am 18.03.2013 19:26, schrieb Rick James: If you are running Apache with MaxClients set too high, that can cause the problem. too high is relative That Apache setting should be something like 20. (Other web servers have similar settings.) 20 is a laughable value as long you are not hosting only sites with no users at all i have seen MaxClients 500 be critical while the hardware was not overloaded and we had THOUSANDS of users which liked to get the website with all it's images, no way with stupid settings of 20 which means only ONE USER at the same time can fetch a single page with images and stylesheets signature.asc Description: OpenPGP digital signature
Re: How to change max simultaneous connection parameter in mysql.
Am 18.03.2013 19:26, schrieb Rick James: If you are running Apache with MaxClients set too high, that can cause the problem. too high is relative That Apache setting should be something like 20. (Other web servers have similar settings.) 20 is a laughable value as long you are not hosting only sites with no users at all i have seen MaxClients 500 be critical while the hardware was not overloaded and we had THOUSANDS of users which liked to get the website with all it's images, no way with stupid settings of 20 which means only ONE USER at the same time can fetch a single page with images and stylesheets signature.asc Description: OpenPGP digital signature
RE: How to change max simultaneous connection parameter in mysql.
20 is plenty if your pages run fast enough. Excess clients after MaxClients are queued in Apache. If the 20 are consuming resources (eg cpu/disk) it is better to queue the excess than to have everybody stumbling over each other. In MySQL, the excess clients beyond max_connections are give an error. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Monday, March 18, 2013 12:15 PM To: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. Am 18.03.2013 19:26, schrieb Rick James: If you are running Apache with MaxClients set too high, that can cause the problem. too high is relative That Apache setting should be something like 20. (Other web servers have similar settings.) 20 is a laughable value as long you are not hosting only sites with no users at all i have seen MaxClients 500 be critical while the hardware was not overloaded and we had THOUSANDS of users which liked to get the website with all it's images, no way with stupid settings of 20 which means only ONE USER at the same time can fetch a single page with images and stylesheets -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to change max simultaneous connection parameter in mysql.
Am 18.03.2013 21:01, schrieb Rick James: 20 is plenty if your pages run fast enough it is not you never have hosted a large site Excess clients after MaxClients are queued in Apache so what - it doe snot help you been there, done that if you have some hundret USERS at the same time any every of them is requesting the same page with a lot of images you are simply DEAD with a limit of 20 in your configuration If the 20 are consuming resources (eg cpu/disk) it is better to queue the excess than to have everybody stumbling over each other. if your server can not serve more than 20 simultaionous requests you are not doing any serious things sorry, 20 can be done with any crappy notebook these days In MySQL, the excess clients beyond max_connections are give an error. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Monday, March 18, 2013 12:15 PM To: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. Am 18.03.2013 19:26, schrieb Rick James: If you are running Apache with MaxClients set too high, that can cause the problem. too high is relative That Apache setting should be something like 20. (Other web servers have similar settings.) 20 is a laughable value as long you are not hosting only sites with no users at all i have seen MaxClients 500 be critical while the hardware was not overloaded and we had THOUSANDS of users which liked to get the website with all it's images, no way with stupid settings of 20 which means only ONE USER at the same time can fetch a single page with images and stylesheets signature.asc Description: OpenPGP digital signature
Re: How to change max simultaneous connection parameter in mysql.
On Mon, 2013-03-18 at 21:35 +0100, Reindl Harald wrote: Am 18.03.2013 21:01, schrieb Rick James: 20 is plenty if your pages run fast enough if your server can not serve more than 20 simultaionous requests you are not doing any serious things or he's using a 286 signature.asc Description: This is a digitally signed message part
RE: How to change max simultaneous connection parameter in mysql.
If you are running Apache with MaxClients set too high, that can cause the problem. That Apache setting should be something like 20. (Other web servers have similar settings.) -Original Message- From: Igor Shevtsov [mailto:nixofort...@gmail.com] Sent: Saturday, March 16, 2013 1:45 AM To: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. Hi Kevin, In your MySQL client pls execute: SET GLOBAL max_connections = 200; To Make the change permanent you can edit /etc/my.cnf or whatever MySQL config file you you've got in your system Look for this line max_connections under [mysqld] secction, add it if it's not in the config. make sure it looks like: max_connections = 200 No MySQL restart required Cheers, Igor On 16/03/13 07:39, Manuel Arostegui wrote: 2013/3/16 Kevin Peterson qh.res...@gmail.com I am using PHP along with mysql. Mysql default configuration allows to have 100 simultaneous connection which I want to chane to 200. Please help. If you're reaching too many connections quite often, this change can imply memory problems in your server. If you are close to get your server to swap...be careful with this parameter as any swapping will affect your performance. Manuel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: How to change max simultaneous connection parameter in mysql.
you never have hosted a large site Check my email address before saying that. 20 may be low, but 100 is rather high. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Monday, March 18, 2013 1:36 PM To: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. Am 18.03.2013 21:01, schrieb Rick James: 20 is plenty if your pages run fast enough it is not you never have hosted a large site Excess clients after MaxClients are queued in Apache so what - it doe snot help you been there, done that if you have some hundret USERS at the same time any every of them is requesting the same page with a lot of images you are simply DEAD with a limit of 20 in your configuration If the 20 are consuming resources (eg cpu/disk) it is better to queue the excess than to have everybody stumbling over each other. if your server can not serve more than 20 simultaionous requests you are not doing any serious things sorry, 20 can be done with any crappy notebook these days In MySQL, the excess clients beyond max_connections are give an error. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: Monday, March 18, 2013 12:15 PM To: mysql@lists.mysql.com Subject: Re: How to change max simultaneous connection parameter in mysql. Am 18.03.2013 19:26, schrieb Rick James: If you are running Apache with MaxClients set too high, that can cause the problem. too high is relative That Apache setting should be something like 20. (Other web servers have similar settings.) 20 is a laughable value as long you are not hosting only sites with no users at all i have seen MaxClients 500 be critical while the hardware was not overloaded and we had THOUSANDS of users which liked to get the website with all it's images, no way with stupid settings of 20 which means only ONE USER at the same time can fetch a single page with images and stylesheets -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to change max simultaneous connection parameter in mysql.
2013/3/16 Kevin Peterson qh.res...@gmail.com I am using PHP along with mysql. Mysql default configuration allows to have 100 simultaneous connection which I want to chane to 200. Please help. If you're reaching too many connections quite often, this change can imply memory problems in your server. If you are close to get your server to swap...be careful with this parameter as any swapping will affect your performance. Manuel. -- Manuel Aróstegui Systems Team tuenti.com
Re: How to change max simultaneous connection parameter in mysql.
Hi Kevin, In your MySQL client pls execute: SET GLOBAL max_connections = 200; To Make the change permanent you can edit /etc/my.cnf or whatever MySQL config file you you've got in your system Look for this line max_connections under [mysqld] secction, add it if it's not in the config. make sure it looks like: max_connections = 200 No MySQL restart required Cheers, Igor On 16/03/13 07:39, Manuel Arostegui wrote: 2013/3/16 Kevin Peterson qh.res...@gmail.com I am using PHP along with mysql. Mysql default configuration allows to have 100 simultaneous connection which I want to chane to 200. Please help. If you're reaching too many connections quite often, this change can imply memory problems in your server. If you are close to get your server to swap...be careful with this parameter as any swapping will affect your performance. Manuel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Retrieve the values from the table of its max date
2012/11/29 11:46 +0530, Trimurthy i have a table which contains the columns date,sname,age,item,quantity,units.my question is i want to retrieve all the values from the table where date=maxdate group by sname how can i get those values. A question, I suspect, found in all SQL courses -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Retrieve the values from the table of its max date
and answered many times on forums.mysql.com -Original Message- From: h...@tbbs.net [mailto:h...@tbbs.net] Sent: Thursday, November 29, 2012 5:40 PM To: mysql@lists.mysql.com Subject: Re: Retrieve the values from the table of its max date 2012/11/29 11:46 +0530, Trimurthy i have a table which contains the columns date,sname,age,item,quantity,units.my question is i want to retrieve all the values from the table where date=maxdate group by sname how can i get those values. A question, I suspect, found in all SQL courses -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Retrieve the values from the table of its max date
perhaps you are looking for something like select entry,timestamp from table A where A.timestamp=(select max(B.timestamp) from table B where a.entry=b.entry); also this oage may be helpful: http://www.artfulsoftware.com/infotree/queries.php re, wh Am 30.11.2012 02:39, schrieb h...@tbbs.net: 2012/11/29 11:46 +0530, Trimurthy i have a table which contains the columns date,sname,age,item,quantity,units.my question is i want to retrieve all the values from the table where date=maxdate group by sname how can i get those values. A question, I suspect, found in all SQL courses -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Default Values for Max User and Max Connections.
Hello All, Question. In mysql 5.5 what are the defaults for these values: max_connections = ? (is this set to 151 in 5,5) max_user_connections = ?(is this set to unlimited unless you provide the exact number)? Thank you so much. Wayne Leutwyler, RHCT Open Source + Open Minds = Open Solutions Courage is being scared to death-but saddling up anyway -John Wayne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Default Values for Max User and Max Connections.
After installing, do SHOW VARIABLES LIKE 'max%'; If you don't like the values, then ad a line to my.cnf (my.ini) and restart. For max_user_connections, I think 0 means unlimited. -Original Message- From: Wayne Leutwyler [mailto:wleut...@columbus.rr.com] Sent: Friday, September 21, 2012 7:04 AM To: mysql@lists.mysql.com Subject: Default Values for Max User and Max Connections. Hello All, Question. In mysql 5.5 what are the defaults for these values: max_connections = ? (is this set to 151 in 5,5) max_user_connections = ? (is this set to unlimited unless you provide the exact number)? Thank you so much. Wayne Leutwyler, RHCT Open Source + Open Minds = Open Solutions Courage is being scared to death-but saddling up anyway -John Wayne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Best way to tune substr,min,max query
I have a query that I need to tune. Basically, substr a text, and select first and last entry. The table is currently a few million rows big. Index is on FromHost (text field) and ReceivedAt (index field) Is the best way to optimize my query. 1) create an index on substr() and the two date columns 2) create some triggering mechanism to just populate this data in a report 3) create table as select I would strongly prefer 1, or something similar/simple so that I wouldn't have to maintain triggers in case they break. I don't like 3 because it's heavy. Thanks, select substr(rsyslog.SystemEvents.FromHost,1,if((locate(_latin1'.',rsyslog.SystemEvents.FromHost) 0),(locate(_latin1'.',rsyslog.SystemEvents.FromHost) - 1),length(rsyslog.SystemEvents.FromHost))) AS hostname, max(rsyslog.SystemEvents.DeviceReportedTime) AS first_syslog_entry, min(rsyslog.SystemEvents.DeviceReportedTime) AS last_syslog_entry from rsyslog.SystemEvents group by substr(rsyslog.SystemEvents.FromHost,1,if((locate(_latin1'.',rsyslog.SystemEvents.FromHost) 0),(locate(_latin1'.',rsyslog.SystemEvents.FromHost) - 1),length(rsyslog.SystemEvents.FromHost)))
Re: max allowed packets on Mac
I agree with michael dykman. 杨涛 我博客1:http://yueliangdao0608.cublog.cn My 我博客2:http://yueliangdao0608.blog.51cto.com 2010/12/18 Michael Dykman mdyk...@gmail.com I'm not nuts about deploying MySQL on macs, but I think you need to assume root. As your mac admin user, try $ sudo su - and give your admin password when asked. You should now be root and can expect permissions to behave as you would expect. - michael dykman On Fri, Dec 17, 2010 at 12:16 PM, g...@noiseunit.com g...@noiseunit.com wrote: Hi, I found this page on the wiki regarding max allowed packets. http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html However, on my Mac OSX when I try to run the following from terminal window I get Permission Denied shell mysqld --max_allowed_packet=16M And if I try to run as sudo but I again get Permission Denied even though I am logged in as Admin on my machine. So I tried to chmod 777 on mysqld but again received an error: chmod: Unable to change file mode on mysqld: Operation not permitted Any help on how I can rectify this would be appreciated. Thanks, Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
Re: max allowed packets on Mac
你出现的太频繁了，兄弟 You post too frequently in the list. 杨涛涛 david.y...@actionsky.com编写： I agree with michael dykman. 杨涛 我博客1:http://yueliangdao0608.cublog.cn My 我博客2:http://yueliangdao0608.blog.51cto.com 2010/12/18 Michael Dykman mdyk...@gmail.com I'm not nuts about deploying MySQL on macs, but I think you need to assume root. As your mac admin user, try $ sudo su - and give your admin password when asked. You should now be root and can expect permissions to behave as you would expect. - michael dykman On Fri, Dec 17, 2010 at 12:16 PM, g...@noiseunit.com g...@noiseunit.com wrote: Hi, I found this page on the wiki regarding max allowed packets. http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html However, on my Mac OSX when I try to run the following from terminal window I get Permission Denied shell mysqld --max_allowed_packet=16M And if I try to run as sudo but I again get Permission Denied even though I am logged in as Admin on my machine. So I tried to chmod 777 on mysqld but again received an error: chmod: Unable to change file mode on mysqld: Operation not permitted Any help on how I can rectify this would be appreciated. Thanks, Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=yueliangdao0...@gmail.com
max allowed packets on Mac
Hi, I found this page on the wiki regarding max allowed packets. http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html However, on my Mac OSX when I try to run the following from terminal window I get Permission Denied shell mysqld --max_allowed_packet=16M And if I try to run as sudo but I again get Permission Denied even though I am logged in as Admin on my machine. So I tried to chmod 777 on mysqld but again received an error: chmod: Unable to change file mode on mysqld: Operation not permitted Any help on how I can rectify this would be appreciated. Thanks, Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: max allowed packets on Mac
I'm not nuts about deploying MySQL on macs, but I think you need to assume root. As your mac admin user, try $ sudo su - and give your admin password when asked. You should now be root and can expect permissions to behave as you would expect. - michael dykman On Fri, Dec 17, 2010 at 12:16 PM, g...@noiseunit.com g...@noiseunit.com wrote: Hi, I found this page on the wiki regarding max allowed packets. http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html However, on my Mac OSX when I try to run the following from terminal window I get Permission Denied shell mysqld --max_allowed_packet=16M And if I try to run as sudo but I again get Permission Denied even though I am logged in as Admin on my machine. So I tried to chmod 777 on mysqld but again received an error: chmod: Unable to change file mode on mysqld: Operation not permitted Any help on how I can rectify this would be appreciated. Thanks, Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Get max ID of each item in a list
I have a simple table mytable id int time_stamp tiemstamp value int tid int I want to find the max id for each individual tid in my table. if I did it one by one it would be: select max(id) from mytable where tid = 12345; but how can I do something more like: select concat(tid,':',id) from mytable where tid in (123,124,125,126); Above is obviously wrone, because what I want is 1 row for each tid in my 'in' clause that is the max id for that tid.: tid:id - 123:8 for instance. Maybe a group by or something?
Re: Get max ID of each item in a list
SELECT tid,max(id) FROM mytable GROUP BY tid Le 10 sept. 2010 à 19:12, Bryan Cantwell a écrit : I have a simple table mytable id int time_stamp tiemstamp value int tid int I want to find the max id for each individual tid in my table. if I did it one by one it would be: select max(id) from mytable where tid = 12345; but how can I do something more like: select concat(tid,':',id) from mytable where tid in (123,124,125,126); Above is obviously wrone, because what I want is 1 row for each tid in my 'in' clause that is the max id for that tid.: tid:id - 123:8 for instance. Maybe a group by or something? -- Kévin Labécot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL 5.0.44 with Innodb Max memory problem :-(
On 7/21/2010 12:16 PM, Nunzio Daveri wrote: database is around 150GB with over 5,000 tables. To make things worse, if I shutdown MySQL, top-c still says all the memory is still used? Is this a bug, why would it say all the memory is used when I turn off MySQL. The weird thing is that when I reboot it is fine and purring. When I start to create reports and Are you sure it's MySQL using up all the memory? If you order top output by size you don't see any other process eating up memory? This is more of a system administration question than MySQL. Any help, guidance or advise is much appreciated. Nunzio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL 5.0.44 with Innodb Max memory problem :-(
Hello Gurus, I just inhereted a Sun 2 U Server with 2 Intel Quad Core CPU's and 16 GB of ram. Here is the problem. The machine is constantly at 99% Memory utilization and we get random row locking, we are only using InnoDB. The database is around 150GB with over 5,000 tables. To make things worse, if I shutdown MySQL, top-c still says all the memory is still used? Is this a bug, why would it say all the memory is used when I turn off MySQL. The weird thing is that when I reboot it is fine and purring. When I start to create reports and run a few SUPER THICK queries, is starts to eat up memory and CPU then once it hits it's 12GB limit that I have setup in the my.cnf file it just stays there. The coders are using indexing for several hundread tables so I am wondering if it just loaded all of the index into memory based on my query and then it just leaves it there, BUT if that is the case why is the OS still max'd out of memory when I shutdown MySQL??? :-( Any help, guidance or advise is much appreciated. Nunzio
How to compute the min and max of two numbers?
I don't see there is a function to compute min or max of two numbers in a row. Essentially I want to select all the rows where the minimum of column1 and column2 are bigger than the maximum of col3 and col4 (The following code won't work as I expected, if I am correct). select * from a_table where min(col1, col2) = max(col3,col4) I could expand the above expression to just using primitive comparisons. But it would not be very readable. I'm wondering if there is a better way to do so in sql. -- Regards, Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to compute the min and max of two numbers?
On Tue, 27 Apr 2010 09:08:29 Peng Yu wrote: I don't see there is a function to compute min or max of two numbers in a row. Essentially I want to select all the rows where the minimum of column1 and column2 are bigger than the maximum of col3 and col4 (The following code won't work as I expected, if I am correct). select * from a_table where min(col1, col2) = max(col3,col4) I think what you are looking for is GREATEST and LEAST. See also http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_least and http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_greatest Best regards, Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to compute the min and max of two numbers?
I want to select all the rows where the minimum of column1 and column2 are bigger than the maximum of col3 and col4 ... WHERE Least(col1,col2) Greatest(col3, col4) ... PB http://www.artfulsoftware.com - Peng Yu wrote: I don't see there is a function to compute min or max of two numbers in a row. Essentially I want to select all the rows where the minimum of column1 and column2 are bigger than the maximum of col3 and col4 (The following code won't work as I expected, if I am correct). select * from a_table where min(col1, col2) = max(col3,col4) I could expand the above expression to just using primitive comparisons. But it would not be very readable. I'm wondering if there is a better way to do so in sql. No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.437 / Virus Database: 271.1.1/2836 - Release Date: 04/26/10 06:31:00
Finding the max integer using MySQL.
Hi there, I would like to find out the maximum (signed or unsigned) integer from MySQL. SELECT CAST( POW(2,100) as UNSIGNED) as max_int; # max_int | 9223372036854775808 This seems to be a MAX_BIGINT from the lookup table at http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html Is there a way to get the MAX_INT ? Is there a constant or a function I can use to get this? I could do SELECT @MAX_SIGNED := POW(2,31) -1 ; but wondering if there is a built in way to do it. I have tested the above on 2 machines. Both linux. (5.0.83 on 64bit) and (5.1.37 on 32bit). Cheers, ~~ c|_| Alister West - Saving the world from coffee! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
max() can't work
select * from table_name where movid = max(movid); why the sql above can't work? Shall I use a sub-select instead? select * from table_name where movid = (select max(movid) from table_name) ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: max() can't work
the field movid is type integer or varchar ? 2010/2/6 tech list bluetm...@gmail.com select * from table_name where movid = max(movid); why the sql above can't work? Shall I use a sub-select instead? select * from table_name where movid = (select max(movid) from table_name) ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=armand...@gmail.com -- ing. paredes aguilar, armando http://www.sinapsisperu.com/ Desarrollador
Re: max() can't work
The max() function is an aggregate function which can be used in conjunction with GROUP BY in the SELECT or HAVING clause: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html . This code should work: select * from table_name group by movid having max(movid). However, there is a simpler and more efficient solution: select * from table_name order by movid desc limit 1. I hope this helps. Best regards Roland Kaber armando wrote: the field movid is type integer or varchar ? 2010/2/6 tech list bluetm...@gmail.com select * from table_name where movid = max(movid); why the sql above can't work? Shall I use a sub-select instead? select * from table_name where movid = (select max(movid) from table_name) ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=armand...@gmail.com
Re: max() can't work
Yes - you must use the subselect. Or, you can set a variable like: select @max := max(movid) from table_name; select * from table_name where movid = @max; On Sat, Feb 6, 2010 at 8:34 AM, tech list bluetm...@gmail.com wrote: select * from table_name where movid = max(movid); why the sql above can't work? Shall I use a sub-select instead? select * from table_name where movid = (select max(movid) from table_name) ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: max() can't work
hi It is not working, select * from table_name group by movid having max(movid) but it is working fine select * from table_name order by movid desc limit 1 From: Roland Kaber roland.ka...@education.lu To: armando armand...@gmail.com Cc: tech list bluetm...@gmail.com; mysql@lists.mysql.com Sent: Sat, 6 February, 2010 8:28:06 PM Subject: Re: max() can't work The max() function is an aggregate function which can be used in conjunction with GROUP BY in the SELECT or HAVING clause: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html . This code should work: select * from table_name group by movid having max(movid). However, there is a simpler and more efficient solution: select * from table_name order by movid desc limit 1. I hope this helps. Best regards Roland Kaber armando wrote: the field movid is type integer or varchar ? 2010/2/6 tech list bluetm...@gmail.com select * from table_name where movid = max(movid); why the sql above can't work? Shall I use a sub-select instead? select * from table_name where movid = (select max(movid) from table_name) ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=armand...@gmail.com The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: max() can't work
Why in the world would you think select * from table_name group by movid having max(movid) would work? It seems to compile without errors but doesn't give you what you seem to want. This would work: select * from table_name group by movid having movid = (select max(movid) from table_name) although then your' not really grouping so the GROUP BY is useless. On Sat, Feb 6, 2010 at 11:01 AM, Vikram A vikkiatb...@yahoo.in wrote: hi It is not working, select * from table_name group by movid having max(movid) but it is working fine select * from table_name order by movid desc limit 1 From: Roland Kaber roland.ka...@education.lu To: armando armand...@gmail.com Cc: tech list bluetm...@gmail.com; mysql@lists.mysql.com Sent: Sat, 6 February, 2010 8:28:06 PM Subject: Re: max() can't work The max() function is an aggregate function which can be used in conjunction with GROUP BY in the SELECT or HAVING clause: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html . This code should work: select * from table_name group by movid having max(movid). However, there is a simpler and more efficient solution: select * from table_name order by movid desc limit 1. I hope this helps. Best regards Roland Kaber armando wrote: the field movid is type integer or varchar ? 2010/2/6 tech list bluetm...@gmail.com select * from table_name where movid = max(movid); why the sql above can't work? Shall I use a sub-select instead? select * from table_name where movid = (select max(movid) from table_name) ? Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=armand...@gmail.com The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Distinct max() and separate unique value
I'm trying to formulate a query on a Wordpress database that will give me the highest 'object_id' with the highest 'term_taxonomy_id', something like: +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1494 | 122 | +-+--+ But I just can't seem to get there? select max(distinct object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id, object_id order by term_taxonomy_id desc, object_id desc +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1481 | 127 | | 300 | 127 | |1503 | 122 | |1494 | 122 | |1470 | 122 | |1468 | 122 | |1205 | 122 | |1062 | 122 | | 316 | 122 | | 306 | 122 | | 228 | 122 | | 222 | 122 | | 216 | 122 | | 211 | 122 | | 184 | 122 | | 155 | 122 | | 149 | 122 | | 134 | 122 | | 128 | 122 | | 124 | 122 | | 119 | 122 | | 113 | 122 | | 109 | 122 | | 105 | 122 | | 93 | 122 | | 91 | 122 | | 87 | 122 | +-+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Distinct max() and separate unique value
I would try: select max(object_id), term_taxonomy_id group by term_taxonomy_id order by term_taxonomy_id; max(column) returns a single value so distinct is not needed. The group by and order by should only have columns thaqt are displayed and that are not aggregate columns. - Original Message - From: Eric Anderson e...@macandbumble.com To: mysql@lists.mysql.com Sent: Tuesday, October 20, 2009 3:42 PM Subject: Distinct max() and separate unique value I'm trying to formulate a query on a Wordpress database that will give me the highest 'object_id' with the highest 'term_taxonomy_id', something like: +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1494 | 122 | +-+--+ But I just can't seem to get there? select max(distinct object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id, object_id order by term_taxonomy_id desc, object_id desc +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1481 | 127 | | 300 | 127 | |1503 | 122 | |1494 | 122 | |1470 | 122 | |1468 | 122 | |1205 | 122 | |1062 | 122 | | 316 | 122 | | 306 | 122 | | 228 | 122 | | 222 | 122 | | 216 | 122 | | 211 | 122 | | 184 | 122 | | 155 | 122 | | 149 | 122 | | 134 | 122 | | 128 | 122 | | 124 | 122 | | 119 | 122 | | 113 | 122 | | 109 | 122 | | 105 | 122 | | 93 | 122 | | 91 | 122 | | 87 | 122 | +-+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@dawiz.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Distinct max() and separate unique value
On Tue, 20 Oct 2009, DaWiz wrote: I would try: select max(object_id), term_taxonomy_id group by term_taxonomy_id order by term_taxonomy_id; max(column) returns a single value so distinct is not needed. The group by and order by should only have columns thaqt are displayed and that are not aggregate columns. You end up with the same object_id. select max(object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id order by term_taxonomy_id; ++--+ | max(object_id) | term_taxonomy_id | ++--+ | 1503 | 122 | | 1503 | 127 | ++--+ I'm trying to formulate a query on a Wordpress database that will give me the highest 'object_id' with the highest 'term_taxonomy_id', something like: +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1494 | 122 | +-+--+ But I just can't seem to get there? select max(distinct object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id, object_id order by term_taxonomy_id desc, object_id desc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Distinct max() and separate unique value
- Original Message - From: Eric Anderson ke...@on-e.com To: mysql@lists.mysql.com Sent: Tuesday, October 20, 2009 4:05 PM Subject: Re: Distinct max() and separate unique value I'm trying to formulate a query on a Wordpress database that will give me the highest 'object_id' with the highest 'term_taxonomy_id', something like: +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1494 | 122 | +-+--+ But I just can't seem to get there? I confess I did not understand what you are trying to do. If what you actually want is the highest 'term_taxonomy_id' for each distinct objhect_id then the query would be: select object_id, max(term_taxonomy_id) where term_taxonomy_id IN (122,127)group by object_id order by object_id desc; This query will not take into consideration term_taxonomy_Id values other than 122 and 127, it also will not return object_id's without a term_taxonomy_Id value of 122 or 127. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
the max length of one SQL statement
Hi guys, Anybody knows that how many bytes the max length of on SQL statement can be in MySQL database? I know it's 64KB in Oracle. Thanks. *^_^* ___ 好玩贺卡等你发，邮箱贺卡全新上线！ http://card.mail.cn.yahoo.com/
Re: the max length of one SQL statement
hi, According to the transfer limit, ... 16M? 2009/3/20 raid fifa raid_f...@yahoo.com.cn: Hi guys, Anybody knows that how many bytes the max length of on SQL statement can be in MySQL database? I know it's 64KB in Oracle. Thanks. *^_^* ___ 好玩贺卡等你发，邮箱贺卡全新上线！ http://card.mail.cn.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: UPDATE ... where max(datecolumn)
Is there ever more than one order per day? I also think you might mean most current order per product, but you didn't say that. On Tue, Jan 27, 2009 at 8:38 AM, Adria Stembridge adrya.stembri...@gmail.com wrote: I have a table like this: ID PRODUCT DATEORDERED 152005-02-18 222008-03-13 322008-11-21 462009-01-21 A field has been added for current order to this table: ID PRODUCT DATEORDERED FLAGCURRENT I need to update all records in the table (about 400,000) so that the most current order has FLAGCURRENT=1 (else 0). Is there an optimal way of doing this with a single query? -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
INNODB and Max Processors
A few weeks back I was reading an article that said that INNODB doesn't take adantage of servers using more than 4 processors. I think I also recieved this as a reply some time ago as to the same thing. I was wondering if this is indeed true. We are using 5.1.30 and wanted to pickup a new dual quad core with 32GB. Before we make the purchase we just want to make sure the database will be able to take advantage of it. Otherwise we will go for the dual core higher speed. This will support hundreds of connections per second and some complicated queries. Overall the data will be less than 50gb so we are looking at more ram to hope that it will support both application and os level caching. Any advice would be greatly appreciated. Gary
Re: INNODB and Max Processors
Gary, I need to know a lot about your workload to say whether it will work well on InnoDB with 4+ processors. You can check http://www.mysqlperformanceblog.com/ for a lot of benchmarks in this area. But in general, my opinion is that for most workloads, 4 total processors (cores included) is reasonable. Not as good as it could be, but reasonable. The only real answer is to benchmark *your* workload and see what happens. And if you run into something that's a weak area, change it -- there are workarounds for many of the trouble spots. However, note that a single query will only ever run on a single core, so if latency is your concern, you need fast, not many. Baron On Fri, Jan 30, 2009 at 12:32 PM, Gary W. Smith g...@primeexalia.com wrote: A few weeks back I was reading an article that said that INNODB doesn't take adantage of servers using more than 4 processors. I think I also recieved this as a reply some time ago as to the same thing. I was wondering if this is indeed true. We are using 5.1.30 and wanted to pickup a new dual quad core with 32GB. Before we make the purchase we just want to make sure the database will be able to take advantage of it. Otherwise we will go for the dual core higher speed. This will support hundreds of connections per second and some complicated queries. Overall the data will be less than 50gb so we are looking at more ram to hope that it will support both application and os level caching. Any advice would be greatly appreciated. Gary -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
UPDATE ... where max(datecolumn)
I have a table like this: ID PRODUCT DATEORDERED 152005-02-18 222008-03-13 322008-11-21 462009-01-21 A field has been added for current order to this table: ID PRODUCT DATEORDERED FLAGCURRENT I need to update all records in the table (about 400,000) so that the most current order has FLAGCURRENT=1 (else 0). Is there an optimal way of doing this with a single query?
how to optimize: max(timetstamp) where a.foo=b.foo ?
hi list, i have tables that look like this( 10.000 entries) : id, timestamp, value to get the latest value for each id i have queries like: select * from tab A where timestamp = (select max(timestamp) from tab B where B.id=A.id) group by id ; on a fast system it takes round 4 sec to complet, but since this is done requlary it slows down to much. the same query takes on a informix system an a slower computer next to nothing. any ideas ? re, walter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to optimize: max(timetstamp) where a.foo=b.foo ?
On Thu, 2008-07-24 at 14:49 +0200, walter harms wrote: hi list, i have tables that look like this( 10.000 entries) : id, timestamp, value to get the latest value for each id i have queries like: select * from tab A where timestamp = (select max(timestamp) from tab B where B.id=A.id) group by id ; on a fast system it takes round 4 sec to complet, but since this is done requlary it slows down to much. the same query takes on a informix system an a slower computer next to nothing. any ideas ? re, walter Run EXPLAIN for the statement on both systems and examine the results. -- Just my 0.0002 million dollars worth, Shawn Where there's duct tape, there's hope. Perl is the duct tape of the Internet. Hassan Schroeder, Sun's first webmaster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to optimize: max(timetstamp) where a.foo=b.foo ?
to get the latest value for each id i have queries like: select * from tab A where timestamp = (select max(timestamp) from tab B where B.id=A.id) group by id ; See Within-group aggregates at http://www.artfulsoftware.com/infotree/queries.php. PB walter harms wrote: hi list, i have tables that look like this( 10.000 entries) : id, timestamp, value to get the latest value for each id i have queries like: select * from tab A where timestamp = (select max(timestamp) from tab B where B.id=A.id) group by id ; on a fast system it takes round 4 sec to complet, but since this is done requlary it slows down to much. the same query takes on a informix system an a slower computer next to nothing. any ideas ? re, walter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to optimize: max(timetstamp) where a.foo=b.foo ?
Peter Brawley wrote: to get the latest value for each id i have queries like: select * from tab A where timestamp = (select max(timestamp) from tab B where B.id=A.id) group by id ; See Within-group aggregates at http://www.artfulsoftware.com/infotree/queries.php. PB hi peter, txh for your hint, is seems that the join .. on stuff works fine. the most tricky part was to realize that max(timestamp) as foo was need so the result could be used with join, (took me 3 sec to find :) ) re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error with max and group by
Thanks, but this is not the result I want. The SQL for the correct result is at http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group.html and I'm apparently using the correct SQL. The problem is that the columns are not coming out correctly - the rows considered for the max function are apparently being mixed up. On Sat, Jun 21, 2008 at 12:00 AM, Perrin Harkins [EMAIL PROTECTED] wrote: On Fri, Jun 20, 2008 at 10:50 PM, Joe Pearl [EMAIL PROTECTED] wrote: I want to get back only the most recent entry for each person and I don't care about the order. I want the result to show Jim with the acq_date of 2008-01-03, Mary and Sally with the location and date for all of them. http://dev.mysql.com/doc/refman/5.0/en/example-maximum-row.html - Perrin -- Arnold Schwarzenegger - I have a love interest in every one of my films - a gun.
Re: Error with max and group by
Your query is just grabbing the max date within the group, but you are not specifying which record you should be pulling for the location, so it's using an arbitrary one within the group. In your query, there is not relation between max date and location. What you need to do is find out what the max date is, and then find out what record that max date belongs to so you can pull the rest of the information from that record. You can do this by use a select on a virtual table. First, get the max date: select name,max(acq_date) AS mx_acq_date from cust_full group by name; Now you want to get the record associate with the matching name/max date, so you need to join the result of the above query with the same table: select name, item_id, location, mx_acq_date from cust_full join (select name,max(acq_date) AS mx_acq_date from cust_full group by name) AS mx_cust_full on cust_full.name=mx_cust_full.name AND cust_full.acq_date=mx_cust_full.mx_acq_date The name+acq_date is going to be your unique string to join on. Your finding out the max, then finding out which record is associated with the max. Brent Baisley I write code. On Jun 20, 2008, at 10:50 PM, Joe Pearl wrote: Hi, My sql is rusty but I'm trying to solve a problem and I'm getting a result that does not make sense. The table is mysql select * from cust_full; +---+-+--++ | name | item_id | location | acq_date | +---+-+--++ | Jim | 1 | OH | 2007-03-15 | | Mary | 2 | PA | 2007-01-15 | | Sally | 1 | OH | 2007-03-15 | | John | 0 | | -00-00 | | Jim | 3 | PA | 2008-01-03 | +---+-+--++ I want to get back only the most recent entry for each person and I don't care about the order. I want the result to show Jim with the acq_date of 2008-01-03, Mary and Sally with the location and date for all of them. However, when I run what I think should be the sql, I get: mysql select name, item_id, location, max(acq_date) from cust_full group by name; +---+-+--+---+ | name | item_id | location | max(acq_date) | +---+-+--+---+ | Jim | 1 | OH | 2008-01-03| | John | 0 | | -00-00| | Mary | 2 | PA | 2007-01-15| | Sally | 1 | OH | 2007-03-15| +---+-+--+---+ Why am I getting the wrong location for Jim? It should be PA. This is on a Mac. joe. 813.528.3859 My LinkedIn profile: http://www.linkedin.com/in/joepearl We could learn a lot from crayons... Some are sharp, some are pretty and some are dull. Some have weird names, and all are different colors, but they all have to live in the same box. - unknown -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error with max and group by
On Sat, Jun 21, 2008 at 8:34 AM, Joe Pearl [EMAIL PROTECTED] wrote: Thanks, but this is not the result I want. I really think it is. You seem to be misunderstanding how GROUP BY works. In any other database than MySQL, the SQL you wrote would simply give an error. In MySQL, it gives you random results for all but the grouped column. The example I pointed to will give you the values from the row with the MAX date for each person. Actually, this example is more complete for you, since it gets all the rows rather than one: http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error with max and group by
Hi, My sql is rusty but I'm trying to solve a problem and I'm getting a result that does not make sense. The table is mysql select * from cust_full; +---+-+--++ | name | item_id | location | acq_date | +---+-+--++ | Jim | 1 | OH | 2007-03-15 | | Mary | 2 | PA | 2007-01-15 | | Sally | 1 | OH | 2007-03-15 | | John | 0 | | -00-00 | | Jim | 3 | PA | 2008-01-03 | +---+-+--++ I want to get back only the most recent entry for each person and I don't care about the order. I want the result to show Jim with the acq_date of 2008-01-03, Mary and Sally with the location and date for all of them. However, when I run what I think should be the sql, I get: mysql select name, item_id, location, max(acq_date) from cust_full group by name; +---+-+--+---+ | name | item_id | location | max(acq_date) | +---+-+--+---+ | Jim | 1 | OH | 2008-01-03| | John | 0 | | -00-00| | Mary | 2 | PA | 2007-01-15| | Sally | 1 | OH | 2007-03-15| +---+-+--+---+ Why am I getting the wrong location for Jim? It should be PA. This is on a Mac. joe. 813.528.3859 My LinkedIn profile: http://www.linkedin.com/in/joepearl We could learn a lot from crayons... Some are sharp, some are pretty and some are dull. Some have weird names, and all are different colors, but they all have to live in the same box. - unknown
Re: Error with max and group by
On Fri, Jun 20, 2008 at 10:50 PM, Joe Pearl [EMAIL PROTECTED] wrote: I want to get back only the most recent entry for each person and I don't care about the order. I want the result to show Jim with the acq_date of 2008-01-03, Mary and Sally with the location and date for all of them. http://dev.mysql.com/doc/refman/5.0/en/example-maximum-row.html - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: mysql query, min, max with where conditions
Well, if your particular problem has a well defined maximum minimum and minimum maximum (Ie the max(q) 4294967296, because q is a 32 bit unsigned int, and min(q) -1) then you can do it without any extra joins or sub selects. select a, b, min(IF(date 100, q, 4294967296)) as min_q, max(IF(date100, q, -1)) as min_q, from a left join b left join c group by a.p - Original Message From: CRISTEA, Adrian [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, June 9, 2008 9:33:37 AM Subject: mysql query, min, max with where conditions Hello there, What is the corect syntax for selecting something like: select a, b, (min(q) where date100), (max(q) where date100) from a left join b left join c group by a.p i need min() max() values each of them with other WHERE clause. How can I do that? Adrian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql query, min, max with where conditions
Hello there, What is the corect syntax for selecting something like: select a, b, (min(q) where date100), (max(q) where date100) from a left join b left join c group by a.p i need min() max() values each of them with other WHERE clause. How can I do that? Adrian. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql query, min, max with where conditions
CRISTEA, Adrian schrieb: Hello there, What is the corect syntax for selecting something like: select a, b, (min(q) where date100), (max(q) where date100) from a left join b left join c group by a.p i need min() max() values each of them with other WHERE clause. How can I do that? sub select or JOIN with condition, g.e.: SELECT MAX(copy_high.q), MIN(copy_low.q) FROM tab_with_date LEFT JOIN tab_with_date AS copy_high ON tab_with_date.pk = copy_high.pk AND copy_high.date 100 LEFT JOIN tab_with_date AS copy_low ON tab_with_date.pk = copy_low.pk AND copy_low.date 100 -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird result on max compared to order by
[EMAIL PROTECTED] schrieb: Hi, I did a select on a primary key.. Select max(account_id) from mytable; -- it gave me a value X I did a select with order by Select account_id from mytable order by account_id desc limit 3 -- it gave me a value of Y ( Y is the right value ) I was wondering why it didn't gave me the same value and after some time doing a select max gave me the right value Y seems your index was corrupted -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Weird result on max compared to order by
Hi, I did a select on a primary key.. Select max(account_id) from mytable; -- it gave me a value X I did a select with order by Select account_id from mytable order by account_id desc limit 3 -- it gave me a value of Y ( Y is the right value ) I was wondering why it didn't gave me the same value and after some time doing a select max gave me the right value Y Tia, This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom it is addressed. It may contain sensitive and private proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you are not the intended recipient, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. FXDirectDealer, LLC reserves the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted. Any reference to the terms of executed transactions should be treated as preliminary only and subject to our formal confirmation. FXDirectDealer, LLC is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.1.23-rc compiled and max open files
Hi ALL, I have compiled MySLQ 5.1.23-rc for Linux 64bit ./configure CC=gcc CFLAGS=-DBIG_JOINS=1 -O3 CXX=g++ CXXFLAGS=-DBIG_JOINS=1 -felide-constructors -fno-exceptions -fno-rtti -O3 --prefix=/app/mysql-5.1.23-rc --localstatedir=/app/mysql-5.1.23-rc/data --libexecdir=/app/mysql-5.1.23-rc/bin --datadir=/app/mysql-5.1.23-rc/data -with-comment=Debian x86_64 --with-server-suffix=Debian x86_64 --enable-shared --enable-static --enable-thread-safe-client --enable-assembler --enable-local-infile --with-big-tables --with-raid --with-mysqld-user=mysql --with-libwrap --with-mysqld-ldflags=-all-static --with-vio --with-bench --with-readline --with-extra-charsets=all --with-innodb --with-isam --with-archive-storage-engine --with-csv-storage-engine --with-federated-storage-engine --with-embedded-privilege-control --with-zlib-dir=bundled --with-ssl=bundled --with-partition 1) While trying to run the sudo bin/mysql_install_db it failed (even though I specified the src directory) sudo bin/mysql_install_db --user=mysql --builddir=/src/mysql-5.1.23-rc --srcdir=/src/mysql-5.1.23-rc --ldata=/app/mysql/data Installing MySQL system tables... Installation of system tables failed! Examine the logs in /app/mysql/data for more information. The logs did not get created; only the test database directory got created (only the directory) I did copy over the mysql-5.1.22-rc/data/mysql table files and after that the MySQL server was able to start. 2) I have tried to specify to mysqld (in the same way of MySQL 5.1.22-rc) to use more open files than limit using mysqld_safe --defaults-file=/app/mysql/etc/my.cnf --open-files-limit=20 and it says [Warning] option 'open_files_limit': unsigned value 20 adjusted to 65535 Is there any way to get around that, please ? Thanks in advance for your help, Mariella Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hi,I want to know how big to configurate the max-connections parameter in my.cnf?
On Dec 22, 2007 10:55 AM, Moon's Father [EMAIL PROTECTED] wrote: how big your mysql connections's users. How big users? don't know what you said. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to know the number of rows used in a SELECT MAX() query? FOUND_ROWS() not working.
Is there a way to know how many rows were used in a computation?
I tried this 'trick' but I still get 1, when I know that there are 3 rows
used...
SELECT SQL_CALC_FOUND_ROWS
MAX(DATE_ADD('2007-10-18 18:04:45', INTERVAL user_access_hours
HOUR)),
MAX(access_expire)
FROM
end_user_groups
JOIN end_user_group_links ON gid = id
WHERE
enabled = 1 AND uid = 16;
select FOUND_ROWS();
http://dev.mysql.com/doc/refman/5.0/en/select.html
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_
found-rows
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to know the number of rows used in a SELECT MAX() query? FOUND_ROWS() not working.
Hi,
Daevid Vincent wrote:
Is there a way to know how many rows were used in a computation?
I tried this 'trick' but I still get 1, when I know that there are 3 rows
used...
SELECT SQL_CALC_FOUND_ROWS
MAX(DATE_ADD('2007-10-18 18:04:45', INTERVAL user_access_hours
HOUR)),
MAX(access_expire)
FROM
end_user_groups
JOIN end_user_group_links ON gid = id
WHERE
enabled = 1 AND uid = 16;
You can use COUNT(*). FOUND_ROWS() works a little differently, as you
know -- it lets you know how many rows would have been returned without
a LIMIT. But this query has no LIMIT of course.
select FOUND_ROWS();
http://dev.mysql.com/doc/refman/5.0/en/select.html
http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_
found-rows
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using MAX function to fetch primary id
You can do it as long as there is only a single record with the max value. If there is more than 1 record with the same max value, there isn't a single record to pull. To do it, you would need to join on the results of the max query, and part of the join condition would be the max value SELECT id, count, cat_id FROM table_x JOIN ( SELECT cat_id, max(count) maxcount FROM table_x GROUP BY cat_id ) AS table_max ON table_x.cat_id=table_max.cat_id AND table_x.count=table_max.maxcount I'm not sure if I got the syntax perfect, but that the concept of how you would do it. On Sep 7, 2007, at 3:11 PM, Cathy Fusko wrote: Hi, I need to select a max value for a group of records and I also need the primary key for that record. I am wondering if this can be done with a single query? e.g Table_x id count cat_id 110 1 220 2 335 2 415 1 with SELECT id, cat_id, max(count) FROM table_x GROUP BY cat_id I would probably get the following result id cat_id max(count) 1 1 15 2 2 35 and what I would like to get is id cat_id max(count) 4 1 15 3 2 35 Is there a way to do this with single query ? cathy www.nachofoto.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using MAX function to fetch primary id
Hi, I need to select a max value for a group of records and I also need the primary key for that record. I am wondering if this can be done with a single query? e.g Table_x id count cat_id 110 1 220 2 335 2 415 1 with SELECT id, cat_id, max(count) FROM table_x GROUP BY cat_id I would probably get the following result id cat_id max(count) 1 1 15 2 2 35 and what I would like to get is id cat_id max(count) 4 1 15 3 2 35 Is there a way to do this with single query ? cathy www.nachofoto.com
RE: Using MAX function to fetch primary id
-Original Message- I need to select a max value for a group of records and I also need the primary key for that record. I am wondering if this can be done with a single query? e.g Table_x id count cat_id 110 1 220 2 335 2 415 1 with SELECT id, cat_id, max(count) FROM table_x GROUP BY cat_id I would probably get the following result id cat_id max(count) 1 1 15 2 2 35 and what I would like to get is id cat_id max(count) 4 1 15 3 2 35 Is there a way to do this with single query ? This is an old old topic and one that causes more grief than it's worth and I wish mySQL would just fix this bug (IMHO it *is* a bug) so it works as people EXPECT it to work... Search the archives for these topics: Erroneus column using MAX() and GROUP BY and Help with subqueries... MAX() and GROUP BY Also these links may help. http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per -group-in-sql/ The short answer is no, or you have to use sub-selects, which in effect is not one select and equally slow and painful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why the max value of innodb_buffer_pool_size is always 4G
I install MySQL on Linux 2.6.9-52bs #2 x86_64 x86_64 x86_64 GNU/Linux, 16G memory with followiing step: 1. ./configure --prefix=/home/mysql/mysql2. make3. make install I set innodb_pool_buffer_size = 8G in my.cnf, but when I use show variables like 'innodb_buffer_pool_size'; the result is : +-++ | Variable_name | Value | +-++ | innodb_buffer_pool_size | 4093640704 | +-++ and I change the innodb_pool_buffer_size to 5G in my.cnf, the result is+-++ | Variable_name | Value | +-++ | innodb_buffer_pool_size | 1996488704 | +-++ why?? chuning
mysqld-max: segfault at and auto restart mysql
Dear All, I have problem that API node on cluster always auto restart mysql (every 30, 20, 15 or just 5 minutes). I see many error messages at message log file: Jun 20 15:27:18 PrivateData1 sshd(pam_unix)[30280]: session opened for user root by root(uid=0) Jun 20 15:51:24 PrivateData1 kernel: mysqld-max[24953]: segfault at 416c7fe8 rip 003d45d68900 rsp 416c8018 error 6 Jun 20 16:03:34 PrivateData1 kernel: mysqld-max[3171]: segfault at 41541fe8 rip 003d45d68900 rsp 41542018 error 6 Jun 20 16:08:50 PrivateData1 kernel: mysqld-max[22925]: segfault at 40de4fe8 rip 003d45d68900 rsp 40de5018 error 6 Jun 20 17:40:56 PrivateData1 kernel: mysqld-max[25183]: segfault at 41d61fe8 rip 003d45d68900 rsp 41d62018 error 6 Jun 20 17:43:59 PrivateData1 kernel: mysqld-max[2592]: segfault at 40e66fe8 rip 003d45d68900 rsp 40e67018 error 6 Jun 20 17:44:14 PrivateData1 kernel: mysqld-max[3047]: segfault at 40b9bfe8 rip 003d45d68900 rsp 40b9c018 error 6 Jun 20 18:53:28 PrivateData1 kernel: mysqld-max[28016]: segfault at 41992fe8 rip 003d45d68900 rsp 41993018 error 6 Jun 20 19:00:55 PrivateData1 kernel: mysqld-max[16842]: segfault at 412b7fe8 rip 003d45d68900 rsp 412b8018 error 6 Jun 20 19:01:40 PrivateData1 kernel: mysqld-max[19158]: segfault at 40fecfe8 rip 003d45d68900 rsp 40fed018 error 6 Jun 21 08:25:35 PrivateData1 kernel: mysqld-max[17855]: segfault at 41febfe8 rip 003d45d68900 rsp 41fec018 error 6 Jun 21 08:30:20 PrivateData1 kernel: mysqld-max[28280]: segfault at 40b5afe8 rip 003d45d68900 rsp 40b5b018 error 6 Jun 21 08:31:33 PrivateData1 kernel: mysqld-max[30318]: segfault at 41276fe8 rip 003d45d68900 rsp 41277018 error 6 but there is no error on the database log file, only restarted messages. My cluser have: 1 MGM node, 1 API node and 2 data nodes. Version: 5.0.37-max | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (March 2, 2007) | | version_comment | MySQL Community Edition - Experimental (GPL) | | version_compile_machine: CentOS 4.4 x86_64, kernel: 2.6.9-34.EL Please help me Thanks so much. Toan Dang
Re: How can I do something like this SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); ?
2007/6/13, Ricardas S [EMAIL PROTECTED]: ops again you probably needed just select greatest(col1,col2,col3) from t order by 1 Thanks, it's work very well. best regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I do something like this SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); ?
Hi, I would like do something like : SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); I know this syntax is wrong but I would like get a solution to this stuff. Thanks for your help. Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I do something like this SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); ?
Have you tried MAX((col1*(MAX_VALUE_OF_COL1+1)+col2)*(MAX_VALUE_OF_COL2+1)+col3) - Original Message - From: KLEIN Stéphane [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 13, 2007 09:30 Subject: How can I do something like this SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); ? Hi, I would like do something like : SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); I know this syntax is wrong but I would like get a solution to this stuff. Thanks for your help. Stephane -- 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: How can I do something like this SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); ?
Ops, small mistake, shoud be MAX((col1*(MAX_VALUE_OF_COL2+1)+col2)*(MAX_VALUE_OF_COL3+1)+col3) - Original Message - From: Ricardas S [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 13, 2007 09:36 Subject: Re: How can I do something like this SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); ? Have you tried MAX((col1*(MAX_VALUE_OF_COL1+1)+col2)*(MAX_VALUE_OF_COL2+1)+col3) - Original Message - From: KLEIN Stéphane [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, June 13, 2007 09:30 Subject: How can I do something like this SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); ? Hi, I would like do something like : SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); I know this syntax is wrong but I would like get a solution to this stuff. Thanks for your help. Stephane -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I do something like this SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); ?
2007/6/13, Ricardas S [EMAIL PROTECTED]: Ops, small mistake, shoud be MAX((col1*(MAX_VALUE_OF_COL2+1)+col2)*(MAX_VALUE_OF_COL3+1)+col3) Sorry, my question is ashamed. Example, I've this row : Col1 | Col2 | Col3 1 | 5 | 8 6 | 2 | 4 12| 13 | 6 After my query, I would like this : Max_value_col 6 8 13 Well, I would like MAX value of cols of one row, not on all the column. I don't know if my explication it more clear. Stephane -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I do something like this SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); ?
select greatest(col1,col2,col3) from (select max(col1) as col1 from t) a, (select max(col2) as col2 from t) b, (select max(col3) as col3 from t) c - Original Message - From: KLEIN Stéphane [EMAIL PROTECTED] To: Ricardas S [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, June 13, 2007 10:00 Subject: Re: How can I do something like this SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); ? 2007/6/13, Ricardas S [EMAIL PROTECTED]: Ops, small mistake, shoud be MAX((col1*(MAX_VALUE_OF_COL2+1)+col2)*(MAX_VALUE_OF_COL3+1)+col3) Sorry, my question is ashamed. Example, I've this row : Col1 | Col2 | Col3 1 | 5 | 8 6 | 2 | 4 12| 13 | 6 After my query, I would like this : Max_value_col 6 8 13 Well, I would like MAX value of cols of one row, not on all the column. I don't know if my explication it more clear. Stephane -- 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: How can I do something like this SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); ?
ops again you probably needed just select greatest(col1,col2,col3) from t order by 1 - Original Message - From: Ricardas S [EMAIL PROTECTED] To: KLEIN Stéphane [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, June 13, 2007 11:02 Subject: Re: How can I do something like this SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); ? select greatest(col1,col2,col3) from (select max(col1) as col1 from t) a, (select max(col2) as col2 from t) b, (select max(col3) as col3 from t) c - Original Message - From: KLEIN Stéphane [EMAIL PROTECTED] To: Ricardas S [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, June 13, 2007 10:00 Subject: Re: How can I do something like this SELECT MAX(col1, col2, col3) FROM mytable ORDER BY MAX(col1, col2, col3); ? 2007/6/13, Ricardas S [EMAIL PROTECTED]: Ops, small mistake, shoud be MAX((col1*(MAX_VALUE_OF_COL2+1)+col2)*(MAX_VALUE_OF_COL3+1)+col3) Sorry, my question is ashamed. Example, I've this row : Col1 | Col2 | Col3 1 | 5 | 8 6 | 2 | 4 12| 13 | 6 After my query, I would like this : Max_value_col 6 8 13 Well, I would like MAX value of cols of one row, not on all the column. I don't know if my explication it more clear. Stephane -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max columns in a tabel in MyISAM storage engine
Hi, Might be column indexing have restriction over number of columns to be indexed, but for number of columns, hope you can overrider with the variables avg_row_length and max_rows during create/alter table. The length/size of the table depends on the file system(maximum file size defined by the OS). Try giving maximum value for avg_row_length. Thanks ViSolve DB Team - Original Message - From: Fabian Köhler [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 17, 2007 4:34 AM Subject: Max columns in a tabel in MyISAM storage engine Hello, i have table with answers to questions. Every answer is a column in the table. i.e. id|q1|q2|q3 1|answer1|answer2|answer5 2|answer3|answer4|asnwer6 another option to save it would be sth like this: id|field|value 1|q1|answer1 1|q2|answer2 1|q3|answer5 2|q1|answer3 ... The last one is not really useable when working with large amounts of data, when you want to select i.e. 200 questions with answers it's 200*nof answers queries to get them. The problem with the first solution is, that MyISAM storage engine is limited to 2599 columns i think. So what's happening if i have more answers than columns available? Does anybody have an idea? VIEWs or GROUP_CONCAT doesn't seem to be an option, they are all to slow. What's the right way to store and select such information? Thank you very much. regards, Fabian -- Fabian Köhler http://www.fabiankoehler.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 269.4.0/762 - Release Date: 4/15/2007 4:22 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max columns in a tabel in MyISAM storage engine
Fabian Köhler wrote: Hello, i have table with answers to questions. Every answer is a column in the table. i.e. id|q1|q2|q3 1|answer1|answer2|answer5 2|answer3|answer4|asnwer6 another option to save it would be sth like this: id|field|value 1|q1|answer1 1|q2|answer2 1|q3|answer5 2|q1|answer3 ... The last one is not really useable when working with large amounts of data, when you want to select i.e. 200 questions with answers it's 200*nof answers queries to get them. Really? Wow, my opinion is that you're trying to do in one table what you should do in two. Have a questions table, and an answers table. The answers table would have a column specifying which question they belong to. i.e. QUESTIONS int autoincrement questionID question ANSWERS int autoincrement answerID int questionID answer Then if you want all the answers to a question, you just grab them: select * from answers where questionID = '100' Or, you can do a join, and get the question information in the same query. -Micah The problem with the first solution is, that MyISAM storage engine is limited to 2599 columns i think. So what's happening if i have more answers than columns available? Does anybody have an idea? VIEWs or GROUP_CONCAT doesn't seem to be an option, they are all to slow. What's the right way to store and select such information? Thank you very much. regards, Fabian -- Fabian Köhler http://www.fabiankoehler.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max columns in a tabel in MyISAM storage engine
Fabian Köhler wrote: Hello, i have table with answers to questions. Every answer is a column in the table. i.e. id|q1|q2|q3 1|answer1|answer2|answer5 2|answer3|answer4|asnwer6 another option to save it would be sth like this: id|field|value 1|q1|answer1 1|q2|answer2 1|q3|answer5 2|q1|answer3 ... The last one is not really useable when working with large amounts of data, when you want to select i.e. 200 questions with answers it's 200*nof answers queries to get them. The problem with the first solution is, that MyISAM storage engine is limited to 2599 columns i think. So what's happening if i have more answers than columns available? Does anybody have an idea? VIEWs or GROUP_CONCAT doesn't seem to be an option, they are all to slow. What's the right way to store and select such information? Thank you very much. regards, Fabian -- Fabian Köhler http://www.fabiankoehler.de Definately curtain number two. It allows you to save space in case of not answered questions, and also to extend your database to be able to handle different query series (i.e. with a different number of questions). The number of database queries depends you need to make to extract the data, depends on the number of question series (or alternitavely the number of users), not the number of answers. You can select all answers in a single query. You'll need a left join in that query. hth -- metastable Stijn Verholen Camille Huysmanslaan 114, bus 2 B-2020 ANTWERPEN +32 (0)3 707 08 08 +32 (0)473 47 62 88 [EMAIL PROTECTED] http://www.metastable.be BTW-BE 0873.645.643 bankrek.nr. ING 363-0106543-77 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max columns in a tabel in MyISAM storage engine
Thanks for all the input. The problem i have with this idea: Really? Wow, my opinion is that you're trying to do in one table what you should do in two. Have a questions table, and an answers table. The answers table would have a column specifying which question they belong to. i.e. QUESTIONS int autoincrement questionID question ANSWERS int autoincrement answerID int questionID answer is, that 1) i must define the column with the value of the answer as a TEXT to cover all possible answers, altough a SMALLINT might fit it better. but i can live with that 2) i think it's slow when i have lots of data. i'm currently not at home to do performance tests, but let's assume i have 2000 questions, then i collect by user input 2000 answers to every question by 1 different users (a realistic scenario for our application) and store it in a table like this: ANSWERS int autoincrement answerID int userID int questionID text answer i then have 2000*1 = 20 mill. rows in ANSWERS. now let's assume i want to select all answers to all questions for 100 random users at once. The only solution i see here for one query is a large join which will take really, really long to get the data even with proper indexes or did i do sth. wrong? is there a way to provide a view on these two tables (QUESTIONS and ANSWERS) that simulate the original table i intended, which is actually fast even with large amounts of data? Are other dbs like oracle better on doing joins on such large tables? So i really like the idea of having one large table with many columns because it's really fast and i can define each column properly but i currently see no option to go this way but with that way there will alway be a limit to the max. amount of answers in the system. regards, Fabian On Mon, 16 Apr 2007 23:39:40 -0700, Micah Stevens [EMAIL PROTECTED] said: Fabian Köhler wrote: Hello, i have table with answers to questions. Every answer is a column in the table. i.e. id|q1|q2|q3 1|answer1|answer2|answer5 2|answer3|answer4|asnwer6 another option to save it would be sth like this: id|field|value 1|q1|answer1 1|q2|answer2 1|q3|answer5 2|q1|answer3 ... The last one is not really useable when working with large amounts of data, when you want to select i.e. 200 questions with answers it's 200*nof answers queries to get them. Really? Wow, my opinion is that you're trying to do in one table what you should do in two. Have a questions table, and an answers table. The answers table would have a column specifying which question they belong to. i.e. QUESTIONS int autoincrement questionID question ANSWERS int autoincrement answerID int questionID answer Then if you want all the answers to a question, you just grab them: select * from answers where questionID = '100' Or, you can do a join, and get the question information in the same query. -Micah The problem with the first solution is, that MyISAM storage engine is limited to 2599 columns i think. So what's happening if i have more answers than columns available? Does anybody have an idea? VIEWs or GROUP_CONCAT doesn't seem to be an option, they are all to slow. What's the right way to store and select such information? Thank you very much. regards, Fabian -- Fabian Köhler http://www.fabiankoehler.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max columns in a tabel in MyISAM storage engine
I also thought about creating a materialized view with mysql by doing: create table vanswers (select ... query to get the table in the format..) or a stored procedure which generates a table like the one below, but all solutions seem to be slow like hell due to the high large joins which are necessary to create such tables. desired table: id|q1|q2|q3 1|answer1|answer2|answer5 2|answer3|answer4|asnwer6 regards, Fabian On Mon, 16 Apr 2007 23:39:40 -0700, Micah Stevens [EMAIL PROTECTED] said: Fabian Köhler wrote: Hello, i have table with answers to questions. Every answer is a column in the table. i.e. id|q1|q2|q3 1|answer1|answer2|answer5 2|answer3|answer4|asnwer6 another option to save it would be sth like this: id|field|value 1|q1|answer1 1|q2|answer2 1|q3|answer5 2|q1|answer3 ... The last one is not really useable when working with large amounts of data, when you want to select i.e. 200 questions with answers it's 200*nof answers queries to get them. Really? Wow, my opinion is that you're trying to do in one table what you should do in two. Have a questions table, and an answers table. The answers table would have a column specifying which question they belong to. i.e. QUESTIONS int autoincrement questionID question ANSWERS int autoincrement answerID int questionID answer Then if you want all the answers to a question, you just grab them: select * from answers where questionID = '100' Or, you can do a join, and get the question information in the same query. -Micah The problem with the first solution is, that MyISAM storage engine is limited to 2599 columns i think. So what's happening if i have more answers than columns available? Does anybody have an idea? VIEWs or GROUP_CONCAT doesn't seem to be an option, they are all to slow. What's the right way to store and select such information? Thank you very much. regards, Fabian -- Fabian Köhler http://www.fabiankoehler.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max columns in a tabel in MyISAM storage engine
There is another question coming to my mind: is it possible to define a view which has more columns then put nof cols limit for storage engine here or does the same limits for a normal table apply to a view? regards, Fabian On Mon, 16 Apr 2007 23:39:40 -0700, Micah Stevens [EMAIL PROTECTED] said: Fabian Köhler wrote: Hello, i have table with answers to questions. Every answer is a column in the table. i.e. id|q1|q2|q3 1|answer1|answer2|answer5 2|answer3|answer4|asnwer6 another option to save it would be sth like this: id|field|value 1|q1|answer1 1|q2|answer2 1|q3|answer5 2|q1|answer3 ... The last one is not really useable when working with large amounts of data, when you want to select i.e. 200 questions with answers it's 200*nof answers queries to get them. Really? Wow, my opinion is that you're trying to do in one table what you should do in two. Have a questions table, and an answers table. The answers table would have a column specifying which question they belong to. i.e. QUESTIONS int autoincrement questionID question ANSWERS int autoincrement answerID int questionID answer Then if you want all the answers to a question, you just grab them: select * from answers where questionID = '100' Or, you can do a join, and get the question information in the same query. -Micah The problem with the first solution is, that MyISAM storage engine is limited to 2599 columns i think. So what's happening if i have more answers than columns available? Does anybody have an idea? VIEWs or GROUP_CONCAT doesn't seem to be an option, they are all to slow. What's the right way to store and select such information? Thank you very much. regards, Fabian -- Fabian Köhler http://www.fabiankoehler.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max columns in a tabel in MyISAM storage engine
Hi, Really? Wow, my opinion is that you're trying to do in one table what you should do in two. Have a questions table, and an answers table. The answers table would have a column specifying which question they belong to. i.e. QUESTIONS int autoincrement questionID question ANSWERS int autoincrement answerID int questionID answer is, that 1) i must define the column with the value of the answer as a TEXT to cover all possible answers, altough a SMALLINT might fit it better. but i can live with that ? why a text? Think: QUESTIONS -- QuestionID QuestionText POSSIBLE_ANSWERS -- QuestionID AnswerID (A/B/C?) AnswerText ANSWERS -- QuestionID AnswerID UserID 2) i think it's slow when i have lots of data. i'm currently not at home to do performance tests, but let's assume i have 2000 questions, then i collect by user input 2000 answers to every question by 1 different users (a realistic scenario for our application) and store it in a table like this: i then have 2000*1 = 20 mill. rows in ANSWERS. now let's assume i want to select all answers to all questions for 100 random users at once. The only solution i see here for one query is a large join which will take really, really long to get the data even with proper indexes or did i do sth. wrong? Getting the answered questions from 100 users should be fast if you use an index on UserID in ANSWERS. is there a way to provide a view on these two tables (QUESTIONS and ANSWERS) that simulate the original table i intended, which is actually fast even with large amounts of data? Are other dbs like oracle better on doing joins on such large tables? So i really like the idea of having one large table with many columns because it's really fast and i can define each column properly but i currently see no option to go this way but with that way there will alway be a limit to the max. amount of answers in the system. Bad idea, if you add a question, you have to modify your metadata AND your queries. This is not how it's supposed to work. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com If you can't dazzle em with brilliance, baffle em with bullshit -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Max columns in a tabel in MyISAM storage engine
If the possible answers are not predetermined, you have a real problem. I don't know how you can optimize that. A full-text index might help, but I don't have a good understanding of the type of queries you're going to need. I am not a good person to ask about performance, nor am I familiar with views (still running 4.1.23 here). Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Fabian Köhler [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 17, 2007 10:09 AM To: 'Jerry Schwartz' Cc: 'mysql@lists.mysql.com' Subject: RE: Max columns in a tabel in MyISAM storage engine The right way to do this would be your second way, where there would be one row per answer. I can't really grasp how the questions and answers relate to anything else, so I'm making a lot of assumptions. If you are concerned that this technique is too slow, because it involves linking two tables, there is an ugly alternative. You could store all of your answers in a single text field, with some kind of delimited format: q1=answer1,q2=answer2,q5=answer5 And then parse it in your program. And eliminates the possibility to do any queries against your database that make any sense whatsoever. i see, so this is the way to go: QUESTIONS -- QuestionID QuestionText POSSIBLE_ANSWERS -- QuestionID AnswerID (A/B/C?) AnswerText ANSWERS -- QuestionID AnswerID UserID but there is still one problem. not every answer got an ID, one can also enter free text as an answer, i'd need to seperate that out in another table again but it's possible of course. I will do some performance testing on this for large data. Regarding my other replies: Because we need to do joins to get the data from that tables above i thought about creating a view to transform the table above into the original idea i had: id|q1|q2 1|a1|a2 2|a3|a4 is this possible with reasonable performance? i only got very slow implementations. also see this link: http://www.wormus.com/aaron/stories/2007/03/23/mysql-restructu ring-data-for-a-view.html i just thought so, because it would be very handy to read and update data that way (altough the updates on that view doesn't seem to be possible at all). Fabian -- Fabian Köhler http://www.fabiankoehler.de -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 17, 2007 3:50 PM To: 'Fabian Köhler'; mysql@lists.mysql.com Subject: RE: Max columns in a tabel in MyISAM storage engine The right way to do this would be your second way, where there would be one row per answer. I can't really grasp how the questions and answers relate to anything else, so I'm making a lot of assumptions. If you are concerned that this technique is too slow, because it involves linking two tables, there is an ugly alternative. You could store all of your answers in a single text field, with some kind of delimited format: q1=answer1,q2=answer2,q5=answer5 And then parse it in your program. Again, I'm not sure where you are going with this. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Fabian Köhler [mailto:[EMAIL PROTECTED] Sent: Monday, April 16, 2007 7:04 PM To: 'mysql@lists.mysql.com' Subject: Max columns in a tabel in MyISAM storage engine Hello, i have table with answers to questions. Every answer is a column in the table. i.e. id|q1|q2|q3 1|answer1|answer2|answer5 2|answer3|answer4|asnwer6 another option to save it would be sth like this: id|field|value 1|q1|answer1 1|q2|answer2 1|q3|answer5 2|q1|answer3 ... The last one is not really useable when working with large amounts of data, when you want to select i.e. 200 questions with answers it's 200*nof answers queries to get them. The problem with the first solution is, that MyISAM storage engine is limited to 2599 columns i think. So what's happening if i have more answers than columns available? Does anybody have an idea? VIEWs or GROUP_CONCAT doesn't seem to be an option, they are all to slow. What's the right way to store and select such information? Thank you very much. regards, Fabian -- Fabian Köhler http://www.fabiankoehler.de -- 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: Max columns in a tabel in MyISAM storage engine
I agree it is poor design, but other than putting a full text index on the answers I'm not sure what can be done. Of course, that might well be faster for retrievals; but it would be slower on insertion. I don't know which would predominate in this case. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 17, 2007 10:13 AM To: mysql@lists.mysql.com Subject: Re: Max columns in a tabel in MyISAM storage engine Not necessarily. I do precisely this for a data base whose structure I do not control. I stuff |code1|code2|code3| ... Into an unused text field. The users can query on LIKE %|code2|%. The key is to have a delimiter at the start and end of the entire list, so that the string match doesn't get confused. Of course, this would probably be painfully slow. I can't say whether or not it would be slower than the other alternatives. It is slow, because it cannot use an index. All in all, bad design IMO, cause you _know_ it's slow beforehand. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max columns in a tabel in MyISAM storage engine
The right way to do this would be your second way, where there would be one row per answer. I can't really grasp how the questions and answers relate to anything else, so I'm making a lot of assumptions. If you are concerned that this technique is too slow, because it involves linking two tables, there is an ugly alternative. You could store all of your answers in a single text field, with some kind of delimited format: q1=answer1,q2=answer2,q5=answer5 And then parse it in your program. And eliminates the possibility to do any queries against your database that make any sense whatsoever. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development 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/[EMAIL PROTECTED]
RE: Max columns in a tabel in MyISAM storage engine
The right way to do this would be your second way, where there would be one row per answer. I can't really grasp how the questions and answers relate to anything else, so I'm making a lot of assumptions. If you are concerned that this technique is too slow, because it involves linking two tables, there is an ugly alternative. You could store all of your answers in a single text field, with some kind of delimited format: q1=answer1,q2=answer2,q5=answer5 And then parse it in your program. Again, I'm not sure where you are going with this. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Fabian Köhler [mailto:[EMAIL PROTECTED] Sent: Monday, April 16, 2007 7:04 PM To: 'mysql@lists.mysql.com' Subject: Max columns in a tabel in MyISAM storage engine Hello, i have table with answers to questions. Every answer is a column in the table. i.e. id|q1|q2|q3 1|answer1|answer2|answer5 2|answer3|answer4|asnwer6 another option to save it would be sth like this: id|field|value 1|q1|answer1 1|q2|answer2 1|q3|answer5 2|q1|answer3 ... The last one is not really useable when working with large amounts of data, when you want to select i.e. 200 questions with answers it's 200*nof answers queries to get them. The problem with the first solution is, that MyISAM storage engine is limited to 2599 columns i think. So what's happening if i have more answers than columns available? Does anybody have an idea? VIEWs or GROUP_CONCAT doesn't seem to be an option, they are all to slow. What's the right way to store and select such information? Thank you very much. regards, Fabian -- Fabian Köhler http://www.fabiankoehler.de -- 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: Max columns in a tabel in MyISAM storage engine
Not necessarily. I do precisely this for a data base whose structure I do not control. I stuff |code1|code2|code3| ... Into an unused text field. The users can query on LIKE %|code2|%. The key is to have a delimiter at the start and end of the entire list, so that the string match doesn't get confused. Of course, this would probably be painfully slow. I can't say whether or not it would be slower than the other alternatives. It is slow, because it cannot use an index. All in all, bad design IMO, cause you _know_ it's slow beforehand. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development 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/[EMAIL PROTECTED]
RE: Max columns in a tabel in MyISAM storage engine
Not necessarily. I do precisely this for a data base whose structure I do not control. I stuff |code1|code2|code3| ... Into an unused text field. The users can query on LIKE %|code2|%. The key is to have a delimiter at the start and end of the entire list, so that the string match doesn't get confused. Of course, this would probably be painfully slow. I can't say whether or not it would be slower than the other alternatives. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 17, 2007 9:53 AM To: mysql@lists.mysql.com Subject: Re: Max columns in a tabel in MyISAM storage engine The right way to do this would be your second way, where there would be one row per answer. I can't really grasp how the questions and answers relate to anything else, so I'm making a lot of assumptions. If you are concerned that this technique is too slow, because it involves linking two tables, there is an ugly alternative. You could store all of your answers in a single text field, with some kind of delimited format: q1=answer1,q2=answer2,q5=answer5 And then parse it in your program. And eliminates the possibility to do any queries against your database that make any sense whatsoever. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Max columns in a tabel in MyISAM storage engine
The right way to do this would be your second way, where there would be one row per answer. I can't really grasp how the questions and answers relate to anything else, so I'm making a lot of assumptions. If you are concerned that this technique is too slow, because it involves linking two tables, there is an ugly alternative. You could store all of your answers in a single text field, with some kind of delimited format: q1=answer1,q2=answer2,q5=answer5 And then parse it in your program. And eliminates the possibility to do any queries against your database that make any sense whatsoever. i see, so this is the way to go: QUESTIONS -- QuestionID QuestionText POSSIBLE_ANSWERS -- QuestionID AnswerID (A/B/C?) AnswerText ANSWERS -- QuestionID AnswerID UserID but there is still one problem. not every answer got an ID, one can also enter free text as an answer, i'd need to seperate that out in another table again but it's possible of course. I will do some performance testing on this for large data. Regarding my other replies: Because we need to do joins to get the data from that tables above i thought about creating a view to transform the table above into the original idea i had: id|q1|q2 1|a1|a2 2|a3|a4 is this possible with reasonable performance? i only got very slow implementations. also see this link: http://www.wormus.com/aaron/stories/2007/03/23/mysql-restructuring-data-for-a-view.html i just thought so, because it would be very handy to read and update data that way (altough the updates on that view doesn't seem to be possible at all). Fabian -- Fabian Köhler http://www.fabiankoehler.de -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 17, 2007 3:50 PM To: 'Fabian Köhler'; mysql@lists.mysql.com Subject: RE: Max columns in a tabel in MyISAM storage engine The right way to do this would be your second way, where there would be one row per answer. I can't really grasp how the questions and answers relate to anything else, so I'm making a lot of assumptions. If you are concerned that this technique is too slow, because it involves linking two tables, there is an ugly alternative. You could store all of your answers in a single text field, with some kind of delimited format: q1=answer1,q2=answer2,q5=answer5 And then parse it in your program. Again, I'm not sure where you are going with this. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Fabian Köhler [mailto:[EMAIL PROTECTED] Sent: Monday, April 16, 2007 7:04 PM To: 'mysql@lists.mysql.com' Subject: Max columns in a tabel in MyISAM storage engine Hello, i have table with answers to questions. Every answer is a column in the table. i.e. id|q1|q2|q3 1|answer1|answer2|answer5 2|answer3|answer4|asnwer6 another option to save it would be sth like this: id|field|value 1|q1|answer1 1|q2|answer2 1|q3|answer5 2|q1|answer3 ... The last one is not really useable when working with large amounts of data, when you want to select i.e. 200 questions with answers it's 200*nof answers queries to get them. The problem with the first solution is, that MyISAM storage engine is limited to 2599 columns i think. So what's happening if i have more answers than columns available? Does anybody have an idea? VIEWs or GROUP_CONCAT doesn't seem to be an option, they are all to slow. What's the right way to store and select such information? Thank you very much. regards, Fabian -- Fabian Köhler http://www.fabiankoehler.de -- 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]
