Optimization question and possible bug
Hi, i'm using MySQL 3.23.37 on a Linux system with an average of about 20 queries/second. First of all i have a little question how i can optimize a query. The query is like that: select * from tbl where key_field1 constant1 and key_field2 constant2 Examine that query with explain select... show that the indexes are not used. The keyfields are unsigned tiny int where every bit has a different meaning. Any clue how i can rewrite this query in a way where the indexes are used? In the database i have a merge-table that merges 10 myisam tables. Sometimes the loadaverage of the system raise above 50 and the long-query-log is filled with some query accessing the merge table. This happens 2-3 times a day. Only a minute later everthing is okay again without doing anything. I can't believe that it is a performance problem because there is a summary of only 10,000 entries in the merge table and 50,000 entries in other tables. Does anybody experienced this strange problem, too? The last thing i found is a possible bug in merge-table implementation of 2.23.41. After an update from 2.23.37 to 2.23.41 i got only the first row of the result set again and again. Maybe it's a communication problem between php (with included mysql interface) and the new MySQL version. (Haven't determined it yet). Thanks St. Pinkert - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Optimization question and possible bug
Stefan Pinkert wrote: Examine that query with explain select... show that the indexes are not used. The keyfields are unsigned tiny int where every bit has a different meaning. Any clue how i can rewrite this query in a way where the indexes are used? If MySQL thinks it will be faster to scan, it will. Make sure you analyze the tables with [my]isamchk -a. If that does not help, please send the output from SHOW INDEX FROM table and the EXPLAIN. In the database i have a merge-table that merges 10 myisam tables. Sometimes the loadaverage of the system raise above 50 and the long-query-log is filled with some query accessing the merge table. This happens 2-3 times a day. Only a minute later everthing is okay again without doing anything. I can't believe that it is a performance problem because there is a summary of only 10,000 entries in the merge table and 50,000 entries in other tables. Does anybody experienced this strange problem, too? Is the long query different than the other queries? Have you done an expain on it? The last thing i found is a possible bug in merge-table implementation of 2.23.41. After an update from 2.23.37 to 2.23.41 i got only the first row of the result set again and again. Maybe it's a communication problem between php (with included mysql interface) and the new MySQL version. (Haven't determined it yet). Don't know. But read this thread just to be sure: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:84077:200108:ilgknliamhblokdjmmhb --Bill - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AW: Optimization question and possible bug
No, they do not differ from the rest. The only thing is that they need longer time to process (between 1 and 4 seconds, normally the SAME query is processed in a few miliseconds). The only correlation i found is that all queries in that log query the merge table. How does mysql handle locks on merge-tables? Maybe it has do to with some locking problembut it's just another suspicion. -Ursprungliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]Im Auftrag von Adams, Bill TQO Gesendet: Freitag, 7. September 2001 17:37 An: Stefan Pinkert Cc: [EMAIL PROTECTED] Betreff: Re: Optimization question and possible bug . In the database i have a merge-table that merges 10 myisam tables. Sometimes the loadaverage of the system raise above 50 and the long-query-log is filled with some query accessing the merge table. This happens 2-3 times a day. Only a minute later everthing is okay again without doing anything. I can't believe that it is a performance problem because there is a summary of only 10,000 entries in the merge table and 50,000 entries in other tables. Does anybody experienced this strange problem, too? Is the long query different than the other queries? Have you done an expain on it? . - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
