Re: left join record in one joined table and not in another
Hi, 2006/1/12, Vincente Aggrippino [EMAIL PROTECTED]: On 1/12/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: That fixed it... Thank you! But I don't understand how. Isn't my implicit inner join the same as the explicit one you used? I read Join Syntax in the ref. manual. Is it related to the new order of precedence for the JOIN operator? comma operator has lower precedence than join now : http://dev.mysql.com/doc/refman/5.0/en/news-5-0-12.html -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Server crashes saying Page directory corruption
Ravi, please open a bug report at http://bugs.mysql.com and attach the COMPLETE UNEDITED .err log to that bug report. Looks like InnoDB accesses a page filled with zeros. This might be a hardware fault or an OS bug, as well as an InnoDB bug. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Ravi Prasad LR [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, January 11, 2006 2:13 PM Subject: Mysql Server crashes saying Page directory corruption Hi all, Server version :4.0.26 OS: FeeBSD-4.10 The mysql server(slave) crashes with the following messages in its error log, and gets restarted, after which rollbacks and starts replicating from master Page directory corruption: supremum not pointed to 051226 10:02:58 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex .(many 0's) ;InnoDB: End of page dump 051226 10:02:58 InnoDB: Page checksum 1575996416, prior-to-4.0.14-form checksum 1371122432 InnoDB: stored checksum 0, prior-to-4.0.14-form stored checksum 0 InnoDB: Page lsn 0 0, low 4 bytes of lsn at page end 0 InnoDB: Error: trying to access a stray pointer 0xc9c1bff8 InnoDB: buf pool start is at 0x52a4000, number of pages 70400 051226 10:02:58InnoDB: Assertion failure in thread 20491 in file ../../innobase/page/../include/buf0buf.ic line 286 InnoDB: We intentionally generate a memory trap InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; it is a slave server replicating from a master. There are no reads happening while server crashed. This has happened couple of times. we tried restoring mysql with a backup of master. But it goes through fine for few days(2-3 days) , after which the same error reiterated. what does this error mean? Please help me in figuring out this error. Thanks , Ravi -- 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]
replica slave created more than 5000 relay-bin file in less than 45 minutes
I got this strange situation, with 2 PC with win2000 and mysql 5.0.18 with the replica enabled. Apparently both the server and slave have no operation to do, but on the slave were created more than 5000 relay-bin file in less than 45 minutes I added some log, also they seems normal. Is the second time this happen today, but I have no idea of what can cause this. Do have someone an idea about that problem ? show full processlist; Id User Hostdb CommandTime State Info -- --- -- --- --- -- - - - 3 root localhost:1969 pmv_manager Sleep82 (NULL) 219 pmv_manager localhost:2505 pmv_manager Sleep44 (NULL) 829 system user (NULL) Connect5033 Queueing master event to the relay log (NULL) 830 system user (NULL) Connect 12781 Reading event from the relay log(NULL) 853 root localhost:4977 (NULL) Query 0 (NULL) mysql show slave status\G *** 1. row *** Slave_IO_State: Queueing master event to the relay log Master_Host: 192.168.201.120 Master_User: replicant Master_Port: 3306 Connect_Retry: 60 Master_Log_File: pmv_manager_log_bin.17 Read_Master_Log_Pos: 4 Relay_Log_File: Arlati-E-relay-bin.005280 Relay_Log_Pos: 4 Relay_Master_Log_File: pmv_manager_log_bin.17 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: pmv_manager.debug_log,pmv_manager.parametri_configu razione,pmv_manager.lista_fep,pmv_manager.stato_fep_slave Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 3312 Relay_Log_Space: 18984128 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 12556 1 row in set (0.30 sec) ** MASTER ** show master status; FilePosition Binlog_Do_DB Binlog_Ignore_DB -- pmv_manager_log_bin.18 271 show binlog events; Log_name Pos Event_type Server_id End_log_pos Info -- -- --- - --- --- pmv_manager_log_bin.18 4 Format_desc 4 98 Server ver: 5.0.18-nt-log, Binlog ver: 4 pmv_manager_log_bin.18 98 Query4 146 use `pmv_manager`; insert into test1 ( idrow, data ) values ( 297, current_timestamp ) pmv_manager_log_bin.18 244 Xid 4 271 COMMIT /* xid=14319 */ Enzo Arlati [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table missing
I've created a database with a table and etc. I'm now getting a msg stating the table has gone missing. Is their a way to either reindex or import the database so the table can show up. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
R: replica slave created more than 5000 relay-bin file in less than 45 minutes
I forgot to add that the reset slave command worked properly , deleting all the relay-bin files. After the reset slave, I restart the slave and the realy files didn't grow in number ( I got only ones of it ). -Messaggio originale- Da: AESYS S.p.A. [Enzo Arlati] [mailto:[EMAIL PROTECTED] Inviato: giovedì 12 gennaio 2006 15.33 A: [EMAIL PROTECTED] Mysql. Com (E-mail) Oggetto: replica slave created more than 5000 relay-bin file in less than 45 minutes I got this strange situation, with 2 PC with win2000 and mysql 5.0.18 with the replica enabled. Apparently both the server and slave have no operation to do, but on the slave were created more than 5000 relay-bin file in less than 45 minutes I added some log, also they seems normal. Is the second time this happen today, but I have no idea of what can cause this. Do have someone an idea about that problem ? show full processlist; Id User Hostdb CommandTime State Info -- --- -- --- --- -- - - - 3 root localhost:1969 pmv_manager Sleep82 (NULL) 219 pmv_manager localhost:2505 pmv_manager Sleep44 (NULL) 829 system user (NULL) Connect5033 Queueing master event to the relay log (NULL) 830 system user (NULL) Connect 12781 Reading event from the relay log(NULL) 853 root localhost:4977 (NULL) Query 0 (NULL) mysql show slave status\G *** 1. row *** Slave_IO_State: Queueing master event to the relay log Master_Host: 192.168.201.120 Master_User: replicant Master_Port: 3306 Connect_Retry: 60 Master_Log_File: pmv_manager_log_bin.17 Read_Master_Log_Pos: 4 Relay_Log_File: Arlati-E-relay-bin.005280 Relay_Log_Pos: 4 Relay_Master_Log_File: pmv_manager_log_bin.17 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: pmv_manager.debug_log,pmv_manager.parametri_configu razione,pmv_manager.lista_fep,pmv_manager.stato_fep_slave Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 3312 Relay_Log_Space: 18984128 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 12556 1 row in set (0.30 sec) ** MASTER ** show master status; FilePosition Binlog_Do_DB Binlog_Ignore_DB -- pmv_manager_log_bin.18 271 show binlog events; Log_name Pos Event_type Server_id End_log_pos Info -- -- --- - --- --- pmv_manager_log_bin.18 4 Format_desc 4 98 Server ver: 5.0.18-nt-log, Binlog ver: 4 pmv_manager_log_bin.18 98 Query4 146 use `pmv_manager`; insert into test1 ( idrow, data ) values ( 297, current_timestamp ) pmv_manager_log_bin.18 244 Xid 4 271 COMMIT /* xid=14319 */ Enzo Arlati [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: table missing
The table may be damaged and seemingly unavaible to languages like php(has your box crashed recently??), run 'SHOW TABLES' from the command prompt and it should appear.If it does try running 'CHECK table' to see if is damaged or not Jon Miller wrote: I've created a database with a table and etc. I'm now getting a msg stating the table has gone missing. Is their a way to either reindex or import the database so the table can show up. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
High Performance MySQL on Amazon
Hi, On Amazon uk there are two versions of the book High Performance MySQL : 1st: High Performance MySQL ~Jeremy D. Zawodny, Derek Balling O'Reilly Paperback - April 23, 2004 £19.95 2nd: High Performance MySQL ~Derek J. Balling O'Reilly Vlg. GmbH Co. Paperback - October 31, 2004 £29.26 Does anyone know the difference between these two books? Has Derek Balling somehow absorbed Jeremy D. Zawodny to become Derek J. Balling? ;) Ian -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: High Performance MySQL on Amazon
Hi Ian, if you look closer at the picture of the second you'll see that it's the German version of the book. /Johan Ian wrote: Hi, On Amazon uk there are two versions of the book High Performance MySQL : 1st: High Performance MySQL ~Jeremy D. Zawodny, Derek Balling O'Reilly Paperback - April 23, 2004 £19.95 2nd: High Performance MySQL ~Derek J. Balling O'Reilly Vlg. GmbH Co. Paperback - October 31, 2004 £29.26 Does anyone know the difference between these two books? Has Derek Balling somehow absorbed Jeremy D. Zawodny to become Derek J. Balling? ;) Ian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: High Performance MySQL on Amazon
Ian wrote: Hi, On Amazon uk there are two versions of the book High Performance MySQL : 1st: High Performance MySQL ~Jeremy D. Zawodny, Derek Balling O'Reilly Paperback - April 23, 2004 £19.95 2nd: High Performance MySQL ~Derek J. Balling O'Reilly Vlg. GmbH Co. Paperback - October 31, 2004 £29.26 - the second one is German language. - ian -- +---+ | Ian Sales Database Administrator | | | | All your database are belong to us | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problems with old isam tables
That looks a little odd. Do isam tables have a .myi file for each table that stores the indexes? If so, yours appears to be missing. I would suggest you use mysqldump to dump the table, change the ENGINE= section in the resulting dump file, and then reload the table and data from that dump file. When upgrading between major versions (4.0, 4.1, 5.0, 5.1, etc), it's always wise to make a full backup of the data files and a full backup of the database using mysqldump. The output from mysqldump is just SQL so it is always the safest route if you run into problems. Greg Fortune On Wednesday 11 January 2006 13:39, Anne Ramey wrote: I have a problem. I had to upgrade to 4.1 yesterday for an application, and now I can't convert my old isam tables to myisam: ALTER TABLE codes TYPE = MYISAM; ERROR 1017 (HY000): Can't find file: 'codes.MYI' (errno: 2) and I can't use phpmyadmin because there are isam tables. Is there any way to save the data and fix this? I know it should have been done when upgrading to 4.0, but for some reason it wasn't. mysql 4.1, redhat ES3 -- Anne pgp323tYrtfbt.pgp Description: PGP signature
Re: problems with old isam tables
Also, you may find that check table/repair table are able to fix the problem. See http://dev.mysql.com/doc/mysql/en/Table_maintenance.html for more information. Greg On Thursday 12 January 2006 08:28, Greg Fortune wrote: That looks a little odd. Do isam tables have a .myi file for each table that stores the indexes? If so, yours appears to be missing. I would suggest you use mysqldump to dump the table, change the ENGINE= section in the resulting dump file, and then reload the table and data from that dump file. When upgrading between major versions (4.0, 4.1, 5.0, 5.1, etc), it's always wise to make a full backup of the data files and a full backup of the database using mysqldump. The output from mysqldump is just SQL so it is always the safest route if you run into problems. Greg Fortune On Wednesday 11 January 2006 13:39, Anne Ramey wrote: I have a problem. I had to upgrade to 4.1 yesterday for an application, and now I can't convert my old isam tables to myisam: ALTER TABLE codes TYPE = MYISAM; ERROR 1017 (HY000): Can't find file: 'codes.MYI' (errno: 2) and I can't use phpmyadmin because there are isam tables. Is there any way to save the data and fix this? I know it should have been done when upgrading to 4.0, but for some reason it wasn't. mysql 4.1, redhat ES3 -- Anne pgpvBPtiiNNqf.pgp Description: PGP signature
Re: left join record in one joined table and not in another
Vincente Aggrippino [EMAIL PROTECTED] wrote on 01/12/2006 12:47:31 AM: On 1/12/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Vincente Aggrippino [EMAIL PROTECTED] wrote on 01/11/2006 11:33:38 PM: I have three tables joined on key fields: delivery is joined with invoice_detail on delivery_id and with location on loc_id. I want to return records of deliveries that have corresponding records in the location table, but have no corresponding records in the invoice table. Here's the query I'm attempting to use: select d.co_id, co.name, count(*) from company co, delivery d left join ( invoice_detail id, location loc ) on ( d.delivery_id = id.delivery_id and d.loc_id = loc.loc_id ) where d.co_id = co.co_id and d.unit_price is not null and unit_chargeis not null and id.delivery_id is null and loc.loc_id is not null group by d.co_id, co.name If I take out either one of the table references in the left join and criteria, it works fine. For example, I can either return deliveries that have corresponding locations or don't have corresponding invoice records. But I can't return records that have locations and don't have invoice records. I get 0 records in the result set. I'm sure I can get this to work if I use something like d.loc_id in ( select ... from location ..., but I want to avoid that if possible because I think that subquery retrieves the entire contents of the table for comparison. Does anyone have any idea why my query isn't working? Any help or ideas are greatly appreciated. First problem: you are mixing implicit CROSS JOINS and explicit LEFT JOINS. As of 5.0.12 that is a recipe for trouble. Here is a repaired version: select d.co_id, co.name, count(*) from company co INNER JOIN delivery d ON d.co_id = co.co_id left join invoice_detail id ON d.delivery_id = id.delivery_id LEFT JOIN location loc ON d.loc_id = loc.loc_id where d.unit_price is not null and unit_chargeis not null and id.delivery_id is null and loc.loc_id is not null group by d.co_id, co.name That may have just fixed it... Try it out and let us know. That fixed it... Thank you! But I don't understand how. Isn't my implicit inner join the same as the explicit one you used? I read Join Syntax in the ref. manual. Is it related to the new order of precedence for the JOIN operator? Shawn Green Database Administrator Unimin Corporation - Spruce Pine The other difference with my original one was that I had both join tables in one left join section. I played with it a little bit. If I separate them like yours, it still works with the implicit cross/inner join... from company co, delivery d left join invoice_detail id on d.delivery_id = id.delivery_id left join location loc on d.loc_id = loc.loc_id Does this method also have a potential problem? Thanks, Vince Yes, your statement is still unstable. Because the comma operator now has a lower precedence than an explicit JOIN command if you wanted to left join to anything other than `delivery` you would need to use parentheses to make sure your JOINs were occurring in the correct sequence. As an example, if you had added something like this to your query: LEFT JOIN other_table ot on ot.company_id = co.id You would more than likely get an error because at the time of the parsing of that third JOIN, the table `company` will not have been added to the hierarchy of tables participating in the query. You would need to add a set of parentheses to explicitly change the order of operations so that `company` becomes part of the query earlier than the comma command normally allows it to be. If all of your JOINs were explicit, that problem goes away because all of your table ref operators will have the same priority (precedence). Also, I do no know for a fact that the on conditional of an implicit JOIN are evaluated before the WHERE clause (at the same time the other explicit ON clauses are being evaluated) or if they are delayed to be evaluated with the other conditions in the WHERE clause. Delaying that evaluation could mean a significant performance hit if you were implicitly joining to just a few records in a rather large table. Try it both ways and see if one is faster than the other. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Question regarding running 'mysql' from a bash script
There is a column in my table with string values, but the strings have spaces in them. I want to read the unique column values into an array in my bash script, so I can use these values in a separate query on this table. How do I get the values into an array? For example, the column teams in my table | team |. Red Sox Chicago Colleens Athletics Kenosha Comets Red Sox and I want to create the equivalent statement, but using what I find in the table declare -a TEAMS=(Red Sox Chicago Colleens Athletics Kenosha Comets) I tried the following: declare -a TEAMS=(`mysql -u $USER -h $HOST --password=$PASSWORD -D $DATABASE -B --disable-column-names --exec select distinct team from registered order by team`) but I get the following array (using the data above) (Red Sox Chicago Colleens Athletics Kenosha Comets) How do I either (1) add quotes around each row entry returned so I get the right array, or (2) fill the array in a different way? Thanks! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C API - mysql_fetch_fields()
I've got a program written for MySQL version 3 (don't ask, long story as to why we haven't upgraded this particular system), compiled using gcc v3.2 on SUSE Linux version 2.4.19-64GB-SMP. I'm getting a segmentation fault when I run using ONE set of input parameters (but not any others). When I run under valgrind, I get the following error just before the segmentation fault: ==21258== Invalid read of size 4 ==21258==at 0x4023BBF7: mysql_fetch_fields (in /usr/lib/libmysqlclient.so.10.0.0) ==21258==Address 0x10 is not stack'd, malloc'd or free'd The particular call in the source code is fields = mysql_fetch_fields(res); where fields is defined as MYSQL_FIELD *fields; Can anyone with more familiarity with the C API internals tell me whether or not mysql_fetch_fields() allocates space for the pointer it returns? This hasn't been an issue in any other programs and my suspicion is that I'm clobbering memory somewhere else (but if it's as simple as me needing to allocate space for the returned pointer, then I'd like to know!) Thanks. -- Dave Weingart, Sr. Programmer/AnalystI can call modules from the vasty RNA Private Label Servicesdeep. -- Why, so can I, or so can Voice: +1-516-682-1470any programmer. But will they run FAX : +1-516-496-0113when you do call for them? This e-mail message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question regarding running 'mysql' from a bash script
Oops - I forgot my version of mysql - 4.0.22 running on Red Hat Linux 7.3 2.96-113 kernel 2.4.20-30.7. Mark On Thursday 12 January 2006 10:43 am, Mark Phillips wrote: There is a column in my table with string values, but the strings have spaces in them. I want to read the unique column values into an array in my bash script, so I can use these values in a separate query on this table. How do I get the values into an array? For example, the column teams in my table | team |. Red Sox Chicago Colleens Athletics Kenosha Comets Red Sox and I want to create the equivalent statement, but using what I find in the table declare -a TEAMS=(Red Sox Chicago Colleens Athletics Kenosha Comets) I tried the following: declare -a TEAMS=(`mysql -u $USER -h $HOST --password=$PASSWORD -D $DATABASE -B --disable-column-names --exec select distinct team from registered order by team`) but I get the following array (using the data above) (Red Sox Chicago Colleens Athletics Kenosha Comets) How do I either (1) add quotes around each row entry returned so I get the right array, or (2) fill the array in a different way? Thanks! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question regarding running 'mysql' from a bash script
one answer to your question as asked would be to wrap the column in a concat() function and put the double quotes around each row. the better answer is to use PERL
RE: Question regarding running 'mysql' from a bash script
Mark, Sql is an alias to mysq -u. What about something like : declare -a TEAMS=(`echo query|sql|sed 's/$/,/g'|sed 's/^//g'|sed 's/$//'`) since the query returns the results 1 per line, the first sed prefixes each line with a quote second sed replaces the newline with quote comma, turning it into 1 line string. Last sed drops off the last quote I gave this command and bash didn't complain :-) so I assume it worked. -Original Message- From: Mark Phillips [mailto:[EMAIL PROTECTED] Sent: Thursday, January 12, 2006 12:44 PM To: MYSQL List Subject: Question regarding running 'mysql' from a bash script There is a column in my table with string values, but the strings have spaces in them. I want to read the unique column values into an array in my bash script, so I can use these values in a separate query on this table. How do I get the values into an array? For example, the column teams in my table | team |. Red Sox Chicago Colleens Athletics Kenosha Comets Red Sox and I want to create the equivalent statement, but using what I find in the table declare -a TEAMS=(Red Sox Chicago Colleens Athletics Kenosha Comets) I tried the following: declare -a TEAMS=(`mysql -u $USER -h $HOST --password=$PASSWORD -D $DATABASE -B --disable-column-names --exec select distinct team from registered order by team`) but I get the following array (using the data above) (Red Sox Chicago Colleens Athletics Kenosha Comets) How do I either (1) add quotes around each row entry returned so I get the right array, or (2) fill the array in a different way? Thanks! -- Mark Phillips Phillips Marketing, Inc [EMAIL PROTECTED] 602 524-0376 480 945-9197 fax -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Query optimization
Howdy all, I've got a question regarding optimizing a query. I've got two tables: listing and listing_city_xref listing is a pretty wide table with many fields that can be queried by users. There is a subset of fields that are always in queries of this table but there are many additional fields that may or maynot be included. The primary key consists of two fields, group_id int and listing_id varchar(30). The listing_city_xref table is very simple. It consists of 3 fields: group_id, listing_id, city_id. All three fields form the PK. There is an index on group_id and listing_id as well as a foreign key that points back to the listing table consisting of these two fields. I created an index on the listing table that contains the group_id, listing_id, and the other fields that all searches use. When I run a query that joins the two tables on the group_id and listing_id and I search only fields that are included in the index that I mentioned the query is really fast. If I add another field that's not included in the index the query slows down by a factor of 100. I can't simply index every field in the listing table so what can I do? I don't understand why added extra critieria to the query destroys its performance. Any ideas? Thanks, Tripp __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question regarding running 'mysql' from a bash script
On 1/12/06, Mark Phillips [EMAIL PROTECTED] wrote: There is a column in my table with string values, but the strings have spaces in them. I want to read the unique column values into an array in my bash script, so I can use these values in a separate query on this table. How do I get the values into an array? For example, the column teams in my table | team |. Red Sox Chicago Colleens Athletics Kenosha Comets Red Sox snip but I get the following array (using the data above) (Red Sox Chicago Colleens Athletics Kenosha Comets) How do I either (1) add quotes around each row entry returned so I get the right array, or (2) fill the array in a different way? Thanks! Use the bash internal variable IFS, Mark: OLDIFS=$IFS IFS=$'\n' for team in `mysql --skip-column-names -B -e SELECT team FROM teamnames; do echo [$team]; done IFS=$OLDIFS enjoy, -jp
Re: Query optimization
It might be a good idea if you could post the results of an EXPLAIN SELECT ... for a fast query and a slow query along with their actual SQL statements. James Harvard At 11:37 am -0800 12/1/06, Tripp Bishop wrote: I've got a question regarding optimizing a query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query optimization
Jim, here's an example query:
SELECT COUNT(listing.listing_id) AS listing_count FROM
listing_city_xref INNER JOIN listing
ON listing.listing_id = listing_city_xref.listing_id
AND listing.group_id = listing_city_xref.group_id
WHERE listing_city_xref.group_id =2
AND listing_city_xref.city_id IN (83,88) AND
listing.price BETWEEN 189000.00 AND 23.00
AND tot_finished_sqft = 1100 AND sqft = 1000 AND
baths = 1 AND bedrooms = 3 AND garage_spaces = 1
AND property_type IN ('RES'));
I created an index on the following fields in the
listing table:
group_id, price, tot_finished_sqft, baths, bedrooms,
garage_spaces, property_type.
Here's the result of the explain on the query above:
| 1 | SIMPLE | listing | ref |
PRIMARY, idx_search | idx_search | 4 | const | 8708 |
Using where|
| 1 | SIMPLE | listing_wiz_city_xref | ref |
PRIMARY, idx_listing, idx_wiz_city | PRIMARY| 36|
const, ahf_test.listing.listing_id |1 | Using
where; Using index |
This query took 12.91 seconds. Running it multiple
times produces similar run times.
Now, if I remove the sqft = 1100 condition from the
where clause I get the following explain output:
| 1 | SIMPLE | listing | range | PRIMARY,
idx_search | idx_search | 15 | NULL | 8688 |
Using where; Using index |
| 1 | SIMPLE | listing_wiz_city_xref | ref |
PRIMARY, idx_listing, idx_wiz_city | PRIMARY| 36
| const, ahf_test.listing.listing_id |1 | Using
where; Using index |
The query executes in 0.09 seconds. Running it
multiple times produces similar runtimes.
One interesting difference between the explain outputs
is that in the second case the ref is NULL. In the
first scenario it is const.
Why would adding extra fields to the query slow the
query down so much? In either case it looks like it's
using the same index.
Thanks,
Tripp
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance Problem on query kind of like a group by
Everyone, I finally feel let down by mysql after 5 years of great use. I break most things in weeks so this is a heck of a record. I am sure I am being a dummy on this, but am wondering if there is some setting somewhere to help out a query like this. Given a table like this: FeatureID is an autonumber and the group field keeps things together aka version control. FeatureID, FeatureGroupID, FeatureName 1, 1, Version 1 2, 1, Version 2 3, 1, Version 3 4, 1, Version 4 ... 999, 1, Version 999 Over small sets I write a query like this and get stuff quickly: Select FeatureName from Feature F where FeatureID = (Select max(FeatureID) from Feature where FeatureGroupID = F.FeatureGroupID) Giving me Verison 999 In MySQL for 1000 versioned records this takes almost half a second on my machine. On my machine for MS SQL this returns right away basically in no time. I make sure cache is not in play by inserting a set of records and then running the query. Also the FeatureGroupID is indexed and so is the featureID. Is there a setting to make this work in mysql administration somewhere? Is there a word or set of words that I can search for? I have looked high and low and have not been able to make this work fast enough for me today. Someone I work with suggested using group by and this seems to be even worse then what I am doing in the above query like seconds or longer. h Also note that also can be considered records in the table such that 1000,2, Version 2 1 1001,2, Version 2 2 1002,2, Version 2 2 The query Select FeatureName from Feature where FeatureID = (Select max(FeatureID) from Feature) will not work for me since i want all of the highest FeatureID'd, FeatureGroupID'd stuff. I have some settings in my my.cnf like: join_buffer_size = 2M read_buffer_size = 2M sort_buffer_size = 3M read_rnd_buffer_size = 3m These have not had any effect. Also caching is not an option because the first hit in one of my more complicated use cases takes over 7 seconds. Someone please help. karma is yours if you can help me on this. Thank You Jim Tyrrell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question regarding running 'mysql' from a bash script
On Thursday 12 January 2006 11:40 am, George Law wrote:
Mark,
Sql is an alias to mysq -u.
What about something like :
declare -a TEAMS=(`echo query|sql|sed 's/$/,/g'|sed 's/^//g'|sed
's/$//'`)
since the query returns the results 1 per line, the first sed prefixes
each line with a quote
second sed replaces the newline with quote comma, turning it into 1 line
string. Last sed drops off the last quote
I gave this command and bash didn't complain :-) so I assume it worked.
I actually ended up with something similar:
declare TEAM_QUERY=`mysql ${ACCESS_PARAMS} --exec select distinct team from
registered order by team`
declare -a TEAMS=(`echo ${TEAM_QUERY}|sed 's/^//g'|sed 's/$//g'`)
Commas are not used in the definition of an array - just spaces between quoted
strings.
Thanks for your help!
-Original Message-
From: Mark Phillips [mailto:[EMAIL PROTECTED]
Sent: Thursday, January 12, 2006 12:44 PM
To: MYSQL List
Subject: Question regarding running 'mysql' from a bash script
There is a column in my table with string values, but the strings have
spaces
in them. I want to read the unique column values into an array in my
bash
script, so I can use these values in a separate query on this table. How
do I
get the values into an array?
For example, the column teams in my table
| team |.
Red Sox
Chicago Colleens
Athletics
Kenosha Comets
Red Sox
and I want to create the equivalent statement, but using what I find in
the
table
declare -a TEAMS=(Red Sox Chicago Colleens Athletics Kenosha
Comets)
I tried the following:
declare -a TEAMS=(`mysql -u $USER -h $HOST --password=$PASSWORD -D
$DATABASE -B --disable-column-names --exec select distinct team from
registered order by team`)
but I get the following array (using the data above)
(Red Sox Chicago Colleens Athletics Kenosha Comets)
How do I either (1) add quotes around each row entry returned so I get
the
right array, or (2) fill the array in a different way?
Thanks!
--
Mark Phillips
Phillips Marketing, Inc
[EMAIL PROTECTED]
602 524-0376
480 945-9197 fax
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Mark Phillips
VP Softball
Arcadia Little League
www.ArcadiaLittleLeague.org
[EMAIL PROTECTED]
602 524-0376
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question regarding running 'mysql' from a bash script
On Thursday 12 January 2006 12:47 pm, Jake Peavy wrote:
On 1/12/06, Mark Phillips [EMAIL PROTECTED] wrote:
There is a column in my table with string values, but the strings have
spaces
in them. I want to read the unique column values into an array in my bash
script, so I can use these values in a separate query on this table. How
do I
get the values into an array?
For example, the column teams in my table
| team |.
Red Sox
Chicago Colleens
Athletics
Kenosha Comets
Red Sox
snip
but I get the following array (using the data above)
(Red Sox Chicago Colleens Athletics Kenosha Comets)
How do I either (1) add quotes around each row entry returned so I get the
right array, or (2) fill the array in a different way?
Thanks!
Use the bash internal variable IFS, Mark:
OLDIFS=$IFS
IFS=$'\n'
for team in `mysql --skip-column-names -B -e SELECT team FROM teamnames;
do
echo [$team];
done
IFS=$OLDIFS
I actually ended up with this:
declare TEAM_QUERY=`mysql ${ACCESS_PARAMS} --exec select distinct team from
registered order by team`
declare -a TEAMS=(`echo ${TEAM_QUERY}|sed 's/^//g'|sed 's/$//g'`)
Thanks for your help!
enjoy,
-jp
--
Mark Phillips
VP Softball
Arcadia Little League
www.ArcadiaLittleLeague.org
[EMAIL PROTECTED]
602 524-0376
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Anyone experimented with CPU affinity for mysqld on multiprocessor and/or hyperthreaded systems?
Like the subject says, I am looking for any info regarding positive or negative effects of using CPU affinity to lock the MySQL process to a single processor (possibly hyperthreaded). Anyone tried this? Learn anything interesting? Thanks, Jeff; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance Problem on query kind of like a group by
Jim Tyrrell wrote:
Everyone,
I finally feel let down by mysql after 5 years of
great use. I break most things in weeks so this is a
heck of a record. I am sure I am being a dummy on
this, but
am wondering if there is some setting somewhere to
help out a query like this.
Given a table like this:
FeatureID is an autonumber and the group field keeps
things together aka version control.
FeatureID, FeatureGroupID, FeatureName
1, 1, Version 1
2, 1, Version 2
3, 1, Version 3
4, 1, Version 4
...
999, 1, Version 999
Over small sets I write a query like this and get
stuff quickly:
Select FeatureName from Feature F where FeatureID =
(Select max(FeatureID) from Feature where
FeatureGroupID = F.FeatureGroupID)
Giving me Verison 999
In MySQL for 1000 versioned records this takes almost
half a second on my machine. On my machine for MS SQL
this returns right away basically in no time. I make
sure cache is not in play by inserting a set of
records and then running the query. Also the
FeatureGroupID is indexed and so is the featureID.
Is there a setting to make this work in mysql
administration somewhere?
Is there a word or set of words that I can search for?
I have looked high and low and have not been able to
make this work fast enough for me today.
Someone I work with suggested using group by and this
seems to be even worse then what I am doing in the
above query like seconds or longer. h
Also note that also can be considered records in the
table such that
1000,2, Version 2 1
1001,2, Version 2 2
1002,2, Version 2 2
The query Select FeatureName from Feature where
FeatureID = (Select max(FeatureID) from Feature)
will not work for me since i want all of the highest
FeatureID'd, FeatureGroupID'd stuff.
I have some settings in my my.cnf like:
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
read_rnd_buffer_size = 3m
These have not had any effect.
Also caching is not an option because the first hit in
one of my more complicated use cases takes over 7
seconds.
Someone please help.
karma is yours if you can help me on this.
Thank You
Jim Tyrrell
Speed questions usually come down to having the right indexes on the tables
involved, and writing queries so the indexes are properly used. The settings in
my.cnf are rarely the issue.
First, you need to find the max FeatureID for each FeatureGroupID. This will go
fastest with a multi-column index on (FeatureGroupID, FeatureID), in that order.
Do you have that? {We'd know if you had included the output of SHOW CREATE
TABLE Feature.} A multi-column index on (FeatureGroupID, FeatureID) will also
function as a single-column index on (FeatureGroupID), so you don't need a
separate index on that column.
Next, you want to find the rows in table Feature whose FeatureGroupID and
FeatureID match the results of step one. This is a frequently asked question,
with 3 solutions given in the manual
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html.
The next question is whether or not mysql is using the index to optimize your
query. Your query is the subquery solution from the above-referenced manual
page. Unfortunately, mysql doesn't always optimize queries with subqueries
properly. To find out what mysql is doing, put EXPLAIN in front of your query
http://dev.mysql.com/doc/refman/4.1/en/explain.html. Post the results if you
need help interpreting them.
If it turns out that the subquery has fooled mysql into not using the index to
help your query, then the temporary table solution in the manual will almost
certainly be faster:
# Replace INT as needed to match the types of these fields in table Feature
CREATE TEMPORARY TABLE max_fids (FeatureGroupID INT, FeatureID INT);
# Avoid anyone changing the data in the middle
LOCK TABLES Feature READ;
# Step 1: Get the max FeatureID for each FeatureGroupID
INSERT INTO max_fids SELECT FeatureGroupID, MAX(FeatureID)
FROM Feature GROUP BY FeatureGroupID;
#Step 2: Find the rows in Feature which match the results in Step 1 via a JOIN
SELECT f.FeatureName,
FROM max_fids m
JOIN Feature f
ON f.FeatureGroupID = m.FeatureGroupID
AND f.FeatureID = m.FeatureID;
#clean up
UNLOCK TABLES;
DROP TABLE max_fids;
That looks a lot more complex, but as the index on (FeatureGroupID, FeatureID)
will almost certainly be used for both steps, it should be very fast.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Check from mysql with autoconf
Hi! The application I develop requires mysqlclient library. What lines should I add to configure.in to make configure script to check for mysql libs and to set proper values for libs and includes? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
