Re: trouble with group by and similar tables
Hi Joerg, Joerg Bruehe wrote: Hi Eben, all ! Eben schrieb: I have the following tables: table1 --- id1 some_field table2 --- id id1 score table3 --- id id1 score I then have the following query: SELECT table1.id,SUM(table2.score) as table2_score FROM table1, table2 WHERE table1.some_field = 'value' AND table2.id1 = table1.id GROUP BY table1.id This works fine and returns each record in table1, grouped by id, with the sum of scores from table2. However, when I do this query: SELECT table1.id,SUM(table2.score) as table2_score, SUM(table3.score) as table3_score FROM table1, table2, table3 WHERE table1.some_field = 'value' AND table2.id1 = table1.id AND table3.id1 = table1.id GROUP BY table1.id The sum'd score values go crazy, reflecting #s that aren't logical. Is the issue that table2 and table3 are identical table structures, or that I simply don't understand how the group by is really working here...? Any advice is appreciated, Sounds weird, and I have no obvious explanation / don't see anything you obviously did wrong. Please tell us the MySQL version you are using, it might help. It is MySQL 5.0.22 Could you describe a bit more exact what you mean by go crazy ? An expected resultset from the first query would look something like: id1 table2_score -- 1 20 However, if I then add in the join for table3, nevermind adding in the SUM statement in the SELECT, it becomes: id1 table2_score -- 1 1500 Is it just that from your data you expect different values, do you get invalid values, or more than any sum of your values could be, or what ? The score values go much higher than they should, the numbers should be relatively low, i.e. 20 50 etc... instead when both tables are joined in, the numbers increase to 1210, 1540, etc... Do both summed values go crazy, or does it still work for the table2 part ? Both summed values go much higher I propose to check that a join between table1 and table3 works correctly, like that between table1 and table2 does. (I would like to know whether it is a problem with table3 by itself, or with the three-table join.) The individual joins work fine, so if I just join table2 or table3, I get back the expected numbers, it's only when I have both tables joined in the statement that it becomes a problem. thanks, Eben Jörg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trouble with group by and similar tables
I have the following tables: table1 --- id1 some_field table2 --- id id1 score table3 --- id id1 score I then have the following query: SELECT table1.id,SUM(table2.score) as table2_score FROM table1, table2 WHERE table1.some_field = 'value' AND table2.id1 = table1.id GROUP BY table1.id This works fine and returns each record in table1, grouped by id, with the sum of scores from table2. However, when I do this query: SELECT table1.id,SUM(table2.score) as table2_score, SUM(table3.score) as table3_score FROM table1, table2, table3 WHERE table1.some_field = 'value' AND table2.id1 = table1.id AND table3.id1 = table1.id GROUP BY table1.id The sum'd score values go crazy, reflecting #s that aren't logical. Is the issue that table2 and table3 are identical table structures, or that I simply don't understand how the group by is really working here...? Any advice is appreciated, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with count in grouped query
I have this query:
SELECT table.id,table.field_1,table.field_2,COUNT(table.field_1) as total
FROM table
WHERE MATCH table.field_1 AGAINST ('some text')
GROUP BY table.field_1,table.field_2
which returns aggregate results like:
1, data..., data..., 3
2, data..., data..., 1
3, data..., data..., 5
etc...
I want to be able to do count of the total records brought back by this
query... but since I am using the group by I can't use COUNT() in the
desired way. So let's say this query returns 15 records, how might I
refactor this query so I can just get back one record showing that it
returned 15 results?
Any suggestions or pointers are appreciated
thanks,
Eben
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help wit query optimization (cont'd)
I left something out, the query looks like:
SELECT SQL_CALC_FOUND_ROWS table_1.id,table_2.*
FROM table_2
LEFT JOIN table_1 ON table_2.shared_id = table_1.shared_id
WHERE MATCH table_2.field AGAINST ('value')
AND table_2.current = 1
AND table_2.shared_id IN (SELECT shared_id FROM table_1_view)
LIMIT 0,10
The resultset is paginated on the front end using the
SQL_CALC_FOUND_ROWS functionality...
thanks,
Eben
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with query optimization
Hi,
I have a query that has to run on a full text indexed table with many
millions of records. I'm trying to figure out some optimizations for
it. Here's the general query:
SELECT SQL_CALC_FOUND_ROWS table_1.id,table_2.*
FROM table_2
LEFT JOIN table_1 ON table_2.shared_id = table_1.shared_id
WHERE MATCH table_2.field AGAINST ('value')
AND table_2.current = 1
AND table_2.shared_id IN (SELECT shared_id FROM table_1_view)
Some details:
1. table_1.shared_id and table_2.shared_id are indexed
2. table_2.current is an indexed tinyint
3. table_1_view is derived from a query like:
SELECT shared_id
FROM table_1
WHERE some_field LIKE 'some_value%'
table_1 is a relatively small table i.e. 100k records
table_2 is massive with 10 million records
Any ideas or suggestions are appreciated
thanks,
Eben
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql crashing on osx 10.3.9
I'm running a dual G5 xserve with OSXS 10.3.9. Mysql version 4.0.24 (the version that ships with the OS). For many months now mysql has been stopping/crashing about twice a week. It's primary purpose in life is to provide the database for a minimally used Horde webmail setup. I've looked at the logs, and can find nothing that offers any indication of what is causing it to go down. I can also attest that it is truly dead, it is not present in the process list, and doing mysqladmin ping reports that there is no running server. Can anyone suggest additional steps for troubleshooting? thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
load balacing in a replicated environment
I am considering setting up replication for a loaded database that has to perform thousands of heavy selects every day. I want to have a master with one way replication to multiple slaves. But I also want to be able to load balance connections across the multiple slaves. What options exist to enable a web server (apache) and or a web application (php/perl) to do load balancing so not all queries are sent to the same slave server, but distributed across all available slaves? One quick and dirty idea would be to have a script that manages the database connection layer, and based on time of day or something passes a different server address through... but there must be sexier ways to handle this sort of thing? any advice is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
processes and threads question
I used to run mysql 3.x on Redhat, and would on any given day have anywhere from 5 to 30 mysqld process/threads going depending on traffic. I upgraded to mysql 4.0.17 on a dedicated redhat box, and now it only ever shows 1 mysqld process/thread that uses max cpu and consumes a good deal of memory. This is all fine, but I'd like to understand what has changed and why I'm not seeing multiple threads like I used to under 3.x? Any insight is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: processes and threads question
I appear to be running kernel 2.4.x ps -ax only shows one mysqld thread John McCaskey wrote: Did you upgrade to a 2.6.x linux kernel as well? Threads get hidden in a normal ps aux command starting in 2.6 and show as a single process. If this is the case do ps aux -L and you will see the threads as well. On Tue, 2005-01-18 at 13:15 -0500, Eben Goodman wrote: I used to run mysql 3.x on Redhat, and would on any given day have anywhere from 5 to 30 mysqld process/threads going depending on traffic. I upgraded to mysql 4.0.17 on a dedicated redhat box, and now it only ever shows 1 mysqld process/thread that uses max cpu and consumes a good deal of memory. This is all fine, but I'd like to understand what has changed and why I'm not seeing multiple threads like I used to under 3.x? Any insight is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: 1040 too many connections
I had the same error for quite some time, the issue seemed to be server resources not being able to kill off connections quickly enough. We had a lot of traffic and some pretty slow queries. Optimizing the queries bought us some time, but ultimately we had to upgrade hardware. This bought us about a year and we are against the wall again now with the 1040 issue. We are probably going to get another server and set up replication. I'd be interested to hear anyone elses stories around this issue, it's been quite a headache for us... Quoting A Z [EMAIL PROTECTED]: Hi, MySQL 4.0.14 This may have been queried a lot here. We get this error and after re-starting the server (MySQL) it seems to go away for a while. As per instructions we have changed the max connection in the My.ini to 500 (max_connections=500). MySQLAdmin displays connections = 120. Is there anything else we can do to deal with this issue? regards ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query syntax in new mysql query browser
I have a sql script that issues a bunch of truncate statements to purge a database in development. The script looks like: truncate table table_1; truncate table tabel_2; truncate table table_3; and so on... I can load this script as a single query in the old mysql_front (version 2.2) GUI and it runs fine. However the same query loaded into MySQL Query Browser fails after the first line. Is there a way to structure the syntax differently so I can run the multiple truncates in one query via Qeury Browser? Any advice is appreciated, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
inserting and displaying unicode characters
I'm running mysql 4.1.7 on linux. I recently made the upgrade from 4.0.1 to 4.1.7 to get the additional character sets support. I have data getting inserted that has various unicode characters embedded such as the Registered Trademark symbol, and various foreign language characters. my tables are innodb and I set them to use utf-8, macroman and macce character sets, and the unicode characters still display in the browser as the incorrect symbols, such as: . If I set the browser to unicode character encoding, then most of the characters display as black diamonds with question marks in them. I am pretty new to this unicode situation and any suggestions/advice would be greatly appreciated. thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
lots of connections and RAM
for a server that is configured for 500 to 1000 max_connections, how much RAM is recommended to support this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: storing large integers properly
yes, some do end in 'x' as I just discovered. Thanks to everyone for the input, I have decided to convert to varchar and re-index. Keith C. Ivey wrote: On 5 Aug 2003 at 9:49, Eben Goodman wrote: The data type of the field I am storing this info in is a bigint(16) unsigned. It appears that isbns that start with 0 are going in as 9 digit numbers, the 0 is being ignored or stripped. I have experienced this before with integer data types ignoring leading 0s. I'm wondering how to address this? Should I change the field to a varchar or char data type? Yes. Phone numbers, zip codes, Social Security numbers, etc., are generally stored as strings, not integers, since they can have leading 0s and you're not going to do calculations on them. Besides, don't some ISBNs end in 'X'? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
storing large integers properly
I am storing book isbn numbers in a table. isbn numbers are 10 digit numbers and many start with 0. The data type of the field I am storing this info in is a bigint(16) unsigned. It appears that isbns that start with 0 are going in as 9 digit numbers, the 0 is being ignored or stripped. I have experienced this before with integer data types ignoring leading 0s. I'm wondering how to address this? Should I change the field to a varchar or char data type? Any advice is appreciated, thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
too many connection error driving me nutz, need advice
I run a site that gets a fair amount of traffic throughout the day. For days at a time the site runs smoothly, and then, at seemingly random intervals I get the mysql error Too many connections. The only solution for this is to stop the mysql service and restart it. I changed the max_connections variable to allow for 300 connections instead of the default 100. When I view processes when the error occurs the process list is totally flooded with mysql processes. I am using php to establish connections and using the mysql_connect() function, not mysql_pconnect. I used to use the pconnect function and it seemed to produce the Too many connections error more frequently. I'm at a bit of a loss with this, any tips on how to address this are greatly appreciated. thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: too many connection error driving me nutz, need advice
This comment confuses me: I have seem some sites code that actually open multiple connections to the same database.. If I am using php's mysql_connect() function, then every time a query is run this function gets run. Wouldn't this mean that for every query a new connection is opened to the same database? thanks, Eben [EMAIL PROTECTED] wrote: Suggestion.. make a small script called closeall.php .. basically it has some code to force closed the mysql connection opened (be sure to run a close for all openened handles) I have seem some sites code that actually open multiple connections to the same database.. Add this file into php.ini under the script_prepend part (gets run after all other scripts are done) .. Perhaps something is getting left open/not closing properly? On Thu, 17 Jul 2003, Eben Goodman wrote: I run a site that gets a fair amount of traffic throughout the day. For days at a time the site runs smoothly, and then, at seemingly random intervals I get the mysql error Too many connections. The only solution for this is to stop the mysql service and restart it. I changed the max_connections variable to allow for 300 connections instead of the default 100. When I view processes when the error occurs the process list is totally flooded with mysql processes. I am using php to establish connections and using the mysql_connect() function, not mysql_pconnect. I used to use the pconnect function and it seemed to produce the Too many connections error more frequently. I'm at a bit of a loss with this, any tips on how to address this are greatly appreciated. thanks, Eben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
