Re: InnoDB speed problems
Matthias, if you can tolerate losing a few last transactions in a power outage or an OS crash, you can set innodb_flush_log_at_trx_commit=2 Have you shut down mysqld and restarted it after populating the tables? MySQL only updates index cardinality statistics when you run ANALYZE TABLE or restart the mysqld server. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, October 01, 2003 6:04 AM Subject: InnoDB speed problems Hi all, Because I want to use transactions in the future I have converted all tables of a copy of our production database server (1800+, 512 MB RAM, Linux) to InnoDB format. No problem until now. First, let me show you settings in my.cnf: key_buffer= 16M table_cache = 128 sort_buffer_size = 1M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 thread_concurrency= 8 innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 20 innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Question: Is sort_buffer_size and read_buffer_size relevant to InnoDB? All these settings seem to be fine for me. With MyISAM I have used a key_buffer of 256M and sort_buffer_size of 4M which procuded very fast database accesses. mytop's output: MySQL on localhost (4.0.15-standard-log) up 0+23:14:39 [04:23:24] Queries: 5.7M qps: 72 Slow:34.0 Se/In/Up/De(%): 63/10/15/05 Cache Hits: 1005.2k Hits/s: 12.3 Ratio: 27.3% Key Efficiency: 100.0% Bps in/out: 8.0k/33.8k But now everything is slow, I don't know why. Even without load each query takes a bit longer. Shouldn't it be vice versa? Then I did some load testing: CPU usage and system load raised by 100 percent. That's not normal for me, does InnoDB need more power, more momory? While testing MySQL was able to handle all the queries but, well, not as fast as I would like to have it in productive environment. I have also noticed that some more complex queries (select with 4 joins and 2 orders) last much too long. With MyISAM everything was 0.5s but now I sometimes have a strange one that is listed for several houndred seconds (?!) in the process list. That's not normal, isn't it? Something strange is going on here and I do not have a clue what I could be. Playing around with the settings and raising InnoDB's pool size to 80% of memory didn't change anything. So, I'm not familiar with this great InnoDB thing, maybe you have some ideas. :) Thanks in advance! Matthias -- 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: InnoDB speed problems
Heikki, if you can tolerate losing a few last transactions in a power outage or an OS crash, you can set innodb_flush_log_at_trx_commit=2 Does that speed up the thing? I should make some testing. Have you shut down mysqld and restarted it after populating the tables? MySQL only updates index cardinality statistics when you run ANALYZE TABLE or restart the mysqld server. Sure, I had several restarts while changing settings and testing again... Best regards Matthias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB speed problems
Hi all, Because I want to use transactions in the future I have converted all tables of a copy of our production database server (1800+, 512 MB RAM, Linux) to InnoDB format. No problem until now. First, let me show you settings in my.cnf: key_buffer= 16M table_cache = 128 sort_buffer_size = 1M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 thread_concurrency= 8 innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 20 innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Question: Is sort_buffer_size and read_buffer_size relevant to InnoDB? All these settings seem to be fine for me. With MyISAM I have used a key_buffer of 256M and sort_buffer_size of 4M which procuded very fast database accesses. mytop's output: MySQL on localhost (4.0.15-standard-log) up 0+23:14:39 [04:23:24] Queries: 5.7M qps: 72 Slow:34.0 Se/In/Up/De(%): 63/10/15/05 Cache Hits: 1005.2k Hits/s: 12.3 Ratio: 27.3% Key Efficiency: 100.0% Bps in/out: 8.0k/33.8k But now everything is slow, I don't know why. Even without load each query takes a bit longer. Shouldn't it be vice versa? Then I did some load testing: CPU usage and system load raised by 100 percent. That's not normal for me, does InnoDB need more power, more momory? While testing MySQL was able to handle all the queries but, well, not as fast as I would like to have it in productive environment. I have also noticed that some more complex queries (select with 4 joins and 2 orders) last much too long. With MyISAM everything was 0.5s but now I sometimes have a strange one that is listed for several houndred seconds (?!) in the process list. That's not normal, isn't it? Something strange is going on here and I do not have a clue what I could be. Playing around with the settings and raising InnoDB's pool size to 80% of memory didn't change anything. So, I'm not familiar with this great InnoDB thing, maybe you have some ideas. :) Thanks in advance! Matthias -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
speed problems (?!)
hi all i've mysql 2.32.52 installed, and there is a table with nearly 2.000.000 records in it (4 field/record). i have 256megs of RAM, and the linux version is RedHat 7.3. i do a simple delete, like: delete from foo_db where foo10; (this is around 15.000 record) and after 30 minutes, still nothing. the load is over 2, minimal disk activity. the filesize is around 300MByte. what could be wrong with this? why is it so slow? - 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: speed problems (?!)
Any reason you can't upgrade to a newer version? mysql,query DSL - 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: speed problems (?!)
On Wed, Sep 25, 2002 at 11:33:37PM +0300, Gergely Imre wrote: hi all i've mysql 2.32.52 installed, and there is a table with nearly 2.000.000 records in it (4 field/record). i have 256megs of RAM, and the linux version is RedHat 7.3. i do a simple delete, like: delete from foo_db where foo10; (this is around 15.000 record) and after 30 minutes, still nothing. the load is over 2, minimal disk activity. the filesize is around 300MByte. what could be wrong with this? why is it so slow? Is foo indexed? Do you have a large key_buffer? What does EXPLAIN SELECT * FROM foo_db WHERE foo 10 say? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 50 days, processed 1,069,481,946 queries (244/sec. avg) - 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: Speed problems.. Still
On Thu, 15 Aug 2002, Benjamin Pflugmann wrote: Let me try to explain. snip Last, an introduction into LEFT JOINs: snip Benjamin, this was a very good explanation. I would love to see an edited version of your post included in the Tutorial section of the very fine manual. Regards, Thomas Spahni -- sql, query - 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
Speed problems.. Still
Ok Im still having some serious speed problems and obviously since more records are being added every day the problem is getting worse. Here is the query: SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company AS Comp, Jobs.JobID, Employers.EmpID FROM Employers INNER JOIN Jobs ON Employers.EmpID = Jobs.EmpID INNER JOIN JobsLocation ON Jobs.JobID = JobsLocation.JobID INNER JOIN Location ON JobsLocation.LocID = Location.LocID WHERE Valid = 1 AND JobTitle IS NOT NULL AND ( LocName LIKE 'US-Alabama%' ) ORDER BY Loc ASC EXPLAIN table type possible_keys key key_len ref rowsExtra Employers ALLPRIMARY NULL NULL NULL 56467 where used; Using temporary; Using filesort Jobsref PRIMARY,EmpID EmpID 4 Employers.EmpID 1 where used JobsLocation ref PRIMARY PRIMARY 4 Jobs.JobID 1 Using index Locationeq_ref LocID,LocName LocID2 JobsLocation.LocID 1 where used And yes I need all of those tables.. I took people's advice and used inner join instead of left join. It takes 52 seconds now... still not even close to satisfactory. Once I speed it up enough I am going to save the results to a table with a timestamp and periodically update those results and call those results if the particular query is being called again within the time limit. Any help would be appreciated. Plus I was told in the last thread that PRIMARY key is not an Index.. Is this true? Thanks! Rick Science without religion is lame, religion without science is blind. - Albert Einstein
Re: Speed problems.. Still
On 14 Aug 2002, at 14:09, Richard Baskett wrote: SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company AS Comp, Jobs.JobID, Employers.EmpID FROM Employers INNER JOIN Jobs ON Employers.EmpID = Jobs.EmpID INNER JOIN JobsLocation ON Jobs.JobID = JobsLocation.JobID INNER JOIN Location ON JobsLocation.LocID = Location.LocID WHERE Valid = 1 AND JobTitle IS NOT NULL AND ( LocName LIKE 'US-Alabama%' ) ORDER BY Loc ASC Maybe try changing the FROM part to ... FROM Location STRAIGHT_JOIN JobsLocation ON JobsLocation.LocID = Location.LocID INNER JOIN Jobs ON Jobs.JobID = JobsLocation.JobID INNER JOIN Employers ON Employers.EmpID = Jobs.EmpID ... to force MySQL to use the Location table first? I'm assuming the location selection is the criterion that will really cut down the number of rows (as opposed to the Valid and JobTitle checks). -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - 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: Speed problems.. Still
Ok that saved me 5 seconds per query! So far so good! I couldn¹t use the STRAIGHT_JOIN though (received errors) so I made it an INNER JOIN. This is the new query: SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company AS Comp, Jobs.JobID, Employers.EmpID FROM Location INNER JOIN JobsLocation ON JobsLocation.LocID = Location.LocID INNER JOIN Jobs ON Jobs.JobID = JobsLocation.JobID INNER JOIN Employers ON Employers.EmpID = Jobs.EmpID WHERE Valid=1 AND JobTitle IS NOT NULL AND (LocName LIKE 'US-Alabama%') ORDER BY Loc ASC The explain still looks the same though... Thanks! Rick Not one of them who took up in his youth with this opinion that there are no gods, ever continued until old age faithful to his conviction. - Plato SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company AS Comp, Jobs.JobID, Employers.EmpID FROM Employers INNER JOIN Jobs ON Employers.EmpID = Jobs.EmpID INNER JOIN JobsLocation ON Jobs.JobID = JobsLocation.JobID INNER JOIN Location ON JobsLocation.LocID = Location.LocID WHERE Valid = 1 AND JobTitle IS NOT NULL AND ( LocName LIKE 'US-Alabama%' ) ORDER BY Loc ASC Maybe try changing the FROM part to ... FROM Location STRAIGHT_JOIN JobsLocation ON JobsLocation.LocID = Location.LocID INNER JOIN Jobs ON Jobs.JobID = JobsLocation.JobID INNER JOIN Employers ON Employers.EmpID = Jobs.EmpID ... to force MySQL to use the Location table first? I'm assuming the location selection is the criterion that will really cut down the number of rows (as opposed to the Valid and JobTitle checks). - 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: Speed problems.. Still
what version of mysql are you running? I was having similar problem then I upgraded my mysql to 3.23.51 and the problem was solved. Richard Baskett wrote: Ok Im still having some serious speed problems and obviously since more records are being added every day the problem is getting worse. Here is the query: SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company AS Comp, Jobs.JobID, Employers.EmpID FROM Employers INNER JOIN Jobs ON Employers.EmpID = Jobs.EmpID INNER JOIN JobsLocation ON Jobs.JobID = JobsLocation.JobID INNER JOIN Location ON JobsLocation.LocID = Location.LocID WHERE Valid = 1 AND JobTitle IS NOT NULL AND ( LocName LIKE 'US-Alabama%' ) ORDER BY Loc ASC EXPLAIN table type possible_keys key key_len ref rowsExtra Employers ALLPRIMARY NULL NULL NULL 56467 where used; Using temporary; Using filesort Jobsref PRIMARY,EmpID EmpID 4 Employers.EmpID 1 where used JobsLocation ref PRIMARY PRIMARY 4 Jobs.JobID 1 Using index Locationeq_ref LocID,LocName LocID2 JobsLocation.LocID 1 where used And yes I need all of those tables.. I took people's advice and used inner join instead of left join. It takes 52 seconds now... still not even close to satisfactory. Once I speed it up enough I am going to save the results to a table with a timestamp and periodically update those results and call those results if the particular query is being called again within the time limit. Any help would be appreciated. Plus I was told in the last thread that PRIMARY key is not an Index.. Is this true? Thanks! Rick Science without religion is lame, religion without science is blind. - Albert Einstein - 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: Speed problems.. Still
I am using 3.23.51 also Dost thou love life? Then do not squander time; for that's the stuff life is made of. - Ben Franklin From: Rahadul Kabir [EMAIL PROTECTED] what version of mysql are you running? I was having similar problem then I upgraded my mysql to 3.23.51 and the problem was solved. Richard Baskett wrote: Ok Im still having some serious speed problems and obviously since more records are being added every day the problem is getting worse. Here is the query: SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company AS Comp, Jobs.JobID, Employers.EmpID FROM Employers INNER JOIN Jobs ON Employers.EmpID = Jobs.EmpID INNER JOIN JobsLocation ON Jobs.JobID = JobsLocation.JobID INNER JOIN Location ON JobsLocation.LocID = Location.LocID WHERE Valid = 1 AND JobTitle IS NOT NULL AND ( LocName LIKE 'US-Alabama%' ) ORDER BY Loc ASC EXPLAIN table type possible_keys key key_len ref rowsExtra Employers ALLPRIMARY NULL NULL NULL 56467 where used; Using temporary; Using filesort Jobsref PRIMARY,EmpID EmpID 4 Employers.EmpID 1 where used JobsLocation ref PRIMARY PRIMARY 4 Jobs.JobID 1 Using index Locationeq_ref LocID,LocName LocID2 JobsLocation.LocID 1 where used And yes I need all of those tables.. I took people's advice and used inner join instead of left join. It takes 52 seconds now... still not even close to satisfactory. Once I speed it up enough I am going to save the results to a table with a timestamp and periodically update those results and call those results if the particular query is being called again within the time limit. Any help would be appreciated. Plus I was told in the last thread that PRIMARY key is not an Index.. Is this true? Thanks! Rick Science without religion is lame, religion without science is blind. - Albert Einstein - 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: Speed problems.. Still
On 14 Aug 2002, at 14:46, Richard Baskett wrote: Ok that saved me 5 seconds per query! So far so good! I couldn¹t use the STRAIGHT_JOIN though (received errors) so I made it an INNER JOIN. This is the new query: You say the EXPLAIN output looks the same, so the 5 second savings is just random. Nothing changed. And sorry about the STRAIGHT_JOIN advice. Looking at the documentation again I see that it doesn't do what I thought it did. SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company AS Comp, Jobs.JobID, Employers.EmpID FROM Location INNER JOIN JobsLocation ON JobsLocation.LocID = Location.LocID INNER JOIN Jobs ON Jobs.JobID = JobsLocation.JobID INNER JOIN Employers ON Employers.EmpID = Jobs.EmpID WHERE Valid=1 AND JobTitle IS NOT NULL AND (LocName LIKE 'US-Alabama%') ORDER BY Loc ASC One more suggestion: Change the first INNER JOIN to a LEFT JOIN. It's a little silly, since you don't really want those nulls, but it should force Location to be selected first. The records with nulls should be eliminated by the later inner joins, or failing that by the JobTitle criterion. I thought there was a better way to control the process, but I don't see it at the moment. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - 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: Speed problems.. Still
If you were here right now I would kiss you! Well not really, but I can not believe what that did!! It takes around 2-4 seconds now for the query to execute completely! I wish I understood when to use what type of join since it is very obvious to me that it matters, and matters greatly! Thank you so much! Here is the new Explain (sorry about the formatting due to email program): LocationrangeLocNameLocName55NULL7where used; Using temporary; Using filesort JobsLocationindexNULLPRIMARY8NULL80732Using index Jobseq_refPRIMARY,EmpIDPRIMARY4JobsLocation.JobID1 where used Employerseq_refPRIMARYPRIMARY4Jobs.EmpID1where used Rick Youth furnishes the materials and plans for the future. Maturity takes and cuts the stones; provided the so-called wisdom of old age doesn't destroy the genius of youth. - Unknown From: Keith C. Ivey [EMAIL PROTECTED] On 14 Aug 2002, at 14:46, Richard Baskett wrote: Ok that saved me 5 seconds per query! So far so good! I couldn¹t use the STRAIGHT_JOIN though (received errors) so I made it an INNER JOIN. This is the new query: You say the EXPLAIN output looks the same, so the 5 second savings is just random. Nothing changed. And sorry about the STRAIGHT_JOIN advice. Looking at the documentation again I see that it doesn't do what I thought it did. SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company AS Comp, Jobs.JobID, Employers.EmpID FROM Location INNER JOIN JobsLocation ON JobsLocation.LocID = Location.LocID INNER JOIN Jobs ON Jobs.JobID = JobsLocation.JobID INNER JOIN Employers ON Employers.EmpID = Jobs.EmpID WHERE Valid=1 AND JobTitle IS NOT NULL AND (LocName LIKE 'US-Alabama%') ORDER BY Loc ASC One more suggestion: Change the first INNER JOIN to a LEFT JOIN. It's a little silly, since you don't really want those nulls, but it should force Location to be selected first. The records with nulls should be eliminated by the later inner joins, or failing that by the JobTitle criterion. I thought there was a better way to control the process, but I don't see it at the moment. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - 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: Speed problems.. Still
I wrote: SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company AS Comp, Jobs.JobID, Employers.EmpID FROM Location INNER JOIN JobsLocation ON JobsLocation.LocID = Location.LocID INNER JOIN Jobs ON Jobs.JobID = JobsLocation.JobID INNER JOIN Employers ON Employers.EmpID = Jobs.EmpID WHERE Valid=1 AND JobTitle IS NOT NULL AND (LocName LIKE 'US-Alabama%') ORDER BY Loc ASC One more suggestion: Change the first INNER JOIN to a LEFT JOIN. It's a little silly, since you don't really want those nulls, but it should force Location to be selected first. The records with nulls should be eliminated by the later inner joins, or failing that by the JobTitle criterion. I thought there was a better way to control the process, but I don't see it at the moment. I found what I was thinking of. There are two different uses of STRAIGHT_JOIN in MySQL SQL. This is the one you need: http://www.mysql.com/doc/en/SELECT.html Instead of changing the first INNER JOIN to a LEFT JOIN, try inserting STRAIGHT_JOIN after the SELECT. It might be somewhat better than the LEFT JOIN solution. No kisses necessary. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - 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: Speed problems.. Still
Hi To help improve the performance, it would help knowing - table structures, use show create table name; - how many records in each table - have all the tables being analyzed/optimized recently Inner joins whilst very useful, do impact on performance. In some cases it is more efficient to use a single simple main query to produce the raw data that can be enhanced by many smaller queries. This approach is usually most valid with queries on very large data sets that return small amounts of data. I hope this helps Simon Richard Baskett wrote: Ok that saved me 5 seconds per query! So far so good! I couldn¹t use the STRAIGHT_JOIN though (received errors) so I made it an INNER JOIN. This is the new query: SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company AS Comp, Jobs.JobID, Employers.EmpID FROM Location INNER JOIN JobsLocation ON JobsLocation.LocID = Location.LocID INNER JOIN Jobs ON Jobs.JobID = JobsLocation.JobID INNER JOIN Employers ON Employers.EmpID = Jobs.EmpID WHERE Valid=1 AND JobTitle IS NOT NULL AND (LocName LIKE 'US-Alabama%') ORDER BY Loc ASC The explain still looks the same though... Thanks! Rick Not one of them who took up in his youth with this opinion that there are no gods, ever continued until old age faithful to his conviction. - Plato SELECT DISTINCT Location.SortID AS Loc, LocName, JobTitle AS Job, Company AS Comp, Jobs.JobID, Employers.EmpID FROM Employers INNER JOIN Jobs ON Employers.EmpID = Jobs.EmpID INNER JOIN JobsLocation ON Jobs.JobID = JobsLocation.JobID INNER JOIN Location ON JobsLocation.LocID = Location.LocID WHERE Valid = 1 AND JobTitle IS NOT NULL AND ( LocName LIKE 'US-Alabama%' ) ORDER BY Loc ASC Maybe try changing the FROM part to ... FROM Location STRAIGHT_JOIN JobsLocation ON JobsLocation.LocID = Location.LocID INNER JOIN Jobs ON Jobs.JobID = JobsLocation.JobID INNER JOIN Employers ON Employers.EmpID = Jobs.EmpID ... to force MySQL to use the Location table first? I'm assuming the location selection is the criterion that will really cut down the number of rows (as opposed to the Valid and JobTitle checks). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Speed problems.. Still
Hi. On Wed 2002-08-14 at 15:06:02 -0700, [EMAIL PROTECTED] wrote: If you were here right now I would kiss you! Well not really, but I can not believe what that did!! It takes around 2-4 seconds now for the query to execute completely! I wish I understood when to use what type of join since it is very obvious to me that it matters, and matters greatly! Thank you so much! Let me try to explain. (1) INNER JOIN is the join you want and the correct one to use. (2) LEFT JOIN produces a different result (and therefore not what you want) and usually takes a bit longer. (3) Your query takes far too long, because MySQL chooses a wrong join order. (the order in which the tables are read in) (4) LEFT JOIN implies a join order and one can (mis-)use it to get around (3) Regarding (2), the wrong result is not a problem in your case, because - as Keith already explained - the additional rows with NULLs for the right table which you get (with a LEFT JOIN compared to an INNER JOIN), are filtered out by other conditions in your query. And the minor speed loss can be neglected, because you gain an order of magnitude of speed at the same time. You still should try to use INNER JOIN instead of the LEFT one. The official way to influence the JOIN order is to use STRAIGHT_JOIN, as Keith explained, i.e. SELECT STRAIGHT_JOIN DISTINCT ... FROM ... Even better would be to try to figure out why MySQL has chosen a suboptimal join order and try to educate it. For the slow query, it guesses that it will need 56467 * 1 * 1 * 1 = 56467 rows, for the fast one it guesses 7 * 80732 * 1 * 1, which are far more, which explains MySQL's choice for the former one. At least one of the two guesses is wrong. Either the slow query looks faster than it is or the fast query looks slower. Most times it is the latter case, but with 56467 rows needing a minute I presume the former. Run ANALYZE TABLE for all involved tables and see if it changes what EXPLAIN tells (for either query). Regardless, for the better join (with the higher guess), EXPLAIN tells that it has to do an full table scan for JobsLocation and cannot use an index (NULL in the possible keys column). You can further improve your query by adding an index on JobsLocation.LocID, and if there is already one, make sure JobsLocation.LocID and Location.LocID have exactly the same type. (Btw, if JobsLocation indeed has no index yet, what column is the PRIMARY KEY on?) Here is the new Explain (sorry about the formatting due to email program): In the future, try EXPLAIN SELECT ... ORDER BY Loc ASC \G \G will result in a column-oriented output which will be longer, but easier to read. Please inform us of any finding. Last, an introduction into LEFT JOINs: In case it is not clear yet, an INNER JOIN creates a cross product, i.e. each row of one table is paired with each row of the other table. The ON clause reduces the result to such pairs you want to see (usally the matching ones). That is also the reason, why you get no rows at all, if one of the tables is empty. There are no rows to be paired in one (or the theoretical explanation via cross product: 0 x some_set = 0) A join via , is the same as INNER JOIN except that you specify the condition in the WHERE clause with all other conditions instead of an seperate ON clause, i.e. SELECT * FROM one, two WHERE one.two_id = two.id has exactly the same meaning (and with MySQL even the same execution path) as SELECT * FROM one INNER JOIN two ON one.two_id = two.id Okay, what does LEFT JOIN do? It takes each row of the left table and pairs it with each row of the right table and chooses the ones you want via ON clause (until here it's the same as INNER JOIN). But if for one row of the left table, there is no row in the right one, so that a pair would satisfy the ON clause, a new pair is created, which contains the normal values of the left table and NULL for all columns of the right table. In other words: LEFT JOIN is an INNER JOIN, which adds (row,NULL) pairs for each row of the left table which has no match (regarding the ON clause) in the right table. Well, when do you need a LEFT JOIN? You only need a LEFT JOIN, if you are interested in non-matches, e.g. you want to join a table with another, which is incomplete in some way and want to ignore that fact, i.e. see all rows of the first table anyhow. In all other cases, use an INNER JOIN. And at last an example: If you have a table with persons and a table with pets. If you want to who has which pet(s), you do SELECT * FROM person, pet WHERE person.pet = pet.id If you want to see all persons and the pets, if any, you need a LEFT JOIN: SELECT * FROM person LEFT JOIN pet ON person.pet = pet.id which displays NULL values if someone has no pet. If you want see who has no pet, you can use this fact: SELECT * FROM person LEFT JOIN pet ON person.pet = pet.id WHERE pet.id IS NULL If you want to see how many pets each person has, you cannot use an
Re: speed problems
[..] Why this is s slow with BSD we still don't know (like i said in my first mail, same query was 3 secs or ~20 sec on Linux) I haven't followed the entire thread so feel free to diss me.. There was problem with the userland threading under early OpenBSD 2.8 versions (including the 'stable' release). This affected MySQL. This is in the docs. Use a 2.8 dated after 2001-01-25 (if I remember correctly) or revert to 2.7. -- Aigars - 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: speed problems
On 2001-03-21, Viljo Marrandi [EMAIL PROTECTED] wrote: No, only one SELECT with 3 joins. Just in case i send this query again: SELECT f.foto_id, f.imgname, f.path FROM foto f, indeks k1, indeks k2 WHERE f.foto_id = k1.foto_id AND k1.word = 'mati' AND f.foto_id = k2.foto_id AND k2.word = 'kose' GROUP BY f.foto_id; [snip; heavy snippage on the following tables to clean them up] 3.22.32 (old and fast) EXPLAIN: +---++---+-+-++- | table | type | possible_keys | key | key_len | ref| rows +---++---+-+-++- | k1| ref| idx2 | idx2| 100 | mati | 986 | f | eq_ref | PRIMARY | PRIMARY | 4 | k1.foto_id |1 | k2| range | idx2 | idx2|NULL | NULL | 1470 3.23.32 (new and slow) EXPLAIN: +---++---+-+-++- | table | type | possible_keys | key | key_len | ref| rows +---++---+-+-++- | k1| ref| idx2 | idx2| 101 | const | 996\ | where used; Using temporary | f | eq_ref | PRIMARY | PRIMARY | 4 | k1.foto_id |1 | k2| ref| idx2 | idx2| 101 | const | 1264 Hm. A couple of things occur to me looking at the above: -the tables in the 3.23.32 example may not be defined the same way. In particular, key_len changes from 100 to 101 bytes for the indeks table lookup. Perhaps a char/varchar is defined NOT NULL on the 3.22 box and not on the 3.23 ? -a temp table is/will be used in 3.23, and not in 3.22. Significance...? -3.22 thinks that there is no index it can use for the k2 join of indeks. Therefore it plans to table-scan through 1470 records. OTOH, 3.23 plans to use a key on the k2 join. But, perhaps (gasping) that causes instead of a single scan through the indeks table, 1,264 seeks from the index file to the data file to the index file... etc? Still, your data set is surely small enough to fit all in disk cache. But perhaps there's also a thousandfold increase in the work done by mysqld at some point during this query? (Grasping again.) In terms of query-optimization, it seems (logically, not necessarily the same as SQLese :) that you really want to join indeks with itself to find indeks.foto_id's which have a word='foo' and a word='bar', and then join that result set with f. Perhaps there's a better way to optimize the query / indexes to reflect that? -- Hank Leininger [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: speed problems
Hello. Sorry about the delay, but I was side-tracked by a server crash at the end of last week. The EXPLAIN for the 3.23 MySQL is at least as good as for 3.22, (in fact, it is better). As one fact, 996*1*1264=1258944 rows are less than 986*1*1470=1449420 rows, but at least in the same magnitude and therefore, at least in theory, the query shouldn't differ much regarding speed. I just reread the thread and saw that you compiled the newer MySQL yourself. That could make part of the speed difference, because TcX' precompiled binaries are using are well tuned (and use pgcc, which is optimized for Pentiums). Could you try a binary from www.mysql.com at least for one of the slow machines and see if it makes a difference? To be true, I am running out of ideas. On the other hand, the test environments have too much differences to easily guess which difference causes the performance hit. :-/ Were all your test cases on different machines, or did some use the same hardware, just with upgraded MySQL? Just to be sure: the machines were not used in another way during the tests, were they? Bye, Benjamin. On Wed, Mar 21, 2001 at 01:10:54PM +0200, [EMAIL PROTECTED] wrote: Okay, that means, memory is no problem. And disks shouldn't be a problem either, because the table fits into memory. (Sorry, don't remember the test case anymore. Did it contain INSERTs/UPDATEs?) No, only one SELECT with 3 joins. Just in case i send this query again: SELECT f.foto_id, f.imgname, f.path FROM foto f, indeks k1, indeks k2 WHERE f.foto_id = k1.foto_id AND k1.word = 'mati' AND f.foto_id = k2.foto_id AND k2.word = 'kose' GROUP BY f.foto_id; This implies, they are not the same for different versions of MySQL? Then, as I said, this is the probably cause for the speed difference. Could you post the output of both, please? Ok, here we go (it didn't fit on one line, sorry): 3.22.32 (old and fast) EXPLAIN: +---++---+-+-++--+---+ | table | type | possible_keys | key | key_len | ref| rows | Extra | +---++---+-+-++--+---+ | k1| ref| idx2 | idx2| 100 | mati | 986 | | | f | eq_ref | PRIMARY | PRIMARY | 4 | k1.foto_id |1 | | | k2| range | idx2 | idx2|NULL | NULL | 1470 | | +---++---+-+-++--+---+ 3.23.32 (new and slow) EXPLAIN: +---++---+-+-++--+-+ | table | type | possible_keys | key | key_len | ref| rows | Extra | +---++---+-+-++--+-+ | k1| ref| idx2 | idx2| 101 | const | 996 | where used; Using temporary | | f | eq_ref | PRIMARY | PRIMARY | 4 | k1.foto_id |1 | | | k2| ref| idx2 | idx2| 101 | const | 1264 | where used | +---++---+-+-++--+-+ Newer 3.23.xx have same EXPLAIN Rgds, Viljo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: speed problems
Hi. Disk speed and memory is more relevant than CPU speed most times, so you should include that info in your comparison. And how big is your table in bytes (not rows). I would start with comparing the output of mysqladmin variables of all installation. Maybe some simply have a bigger key buffer or something like this. Next, compare the output of EXPLAIN for all machines. In any case, it should be the same for the same MySQL versions. If it differs for any of the test cases, this could cause the speed difference. Bye, Benjamin. On Tue, Mar 20, 2001 at 07:04:09PM +0200, [EMAIL PROTECTED] wrote: Hello, I encountered following problem: when using older mysql (3.22.23 vs 3.23.33) then older is about 4-6 times faster on the same machine. What could be the problem? Sytem is Debian 2.2r2, older mysql was from .deb but newer i built myself. The query looks like this: SELECT f.foto_id, f.imgname, f.path FROM foto f, indeks k1, indeks k2 WHERE f.foto_id = k1.foto_id AND k1.word = 'mati' AND f.foto_id = k2.foto_id AND k2.word = 'kose' GROUP BY f.foto_id; Where foto has ~8000 rows and indeks has ~15 rows (words) and result has 1266 rows. Here are some testing results on different machines and op. systems: 3.22.32- 3 sec (Debian, PIII 600) 3.22.32- 6 sec (Debian, Cel 333A) 3.23.32- 24 sec (RH7, Dual PIII 500) 3.23.33- 36 sec (Debian, Cel 333A) 3.23.29a-gamma - 71 sec (FreeBSD, PIII 550) 3.23.29a-gamma - 90 sec (FreeBSD, Cel 333A) And what is most amazing... It's SOO slow on BSD. I wonder why? Or are there any ways to make this query faster? Sometimes there will be even 3 or more words (here only 'mati' and 'kose'). - 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: speed problems
Disk speed and memory is more relevant than CPU speed most times, so you should include that info in your comparison. This is the fun part :). Machine which is the fastest with old mysql and Debian has IDE HDD and 128 MB RAM, RH7 machine (dual PIII and new mysql) has SCSI RAID and 512 MB RAM, same with FreeBSD server. And how big is your table in bytes (not rows). Little more than 12 MB I would start with comparing the output of mysqladmin variables Already did this. Machines with new mysql have ALL bigger values (key-buffer, sort-buffer etc) than this old mysql. And we tested them with same values too... nothing, still alot slower :( Next, compare the output of EXPLAIN for all machines. In any case, it should be the same for the same MySQL versions. If it differs for any of the test cases, this could cause the speed difference. Yes, they are same with same versions of MySQL. Rgds, Viljo - 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: speed problems
Hi. On Wed, Mar 21, 2001 at 11:57:09AM +0200, [EMAIL PROTECTED] wrote: Disk speed and memory is more relevant than CPU speed most times, so you should include that info in your comparison. This is the fun part :). Machine which is the fastest with old mysql and Debian has IDE HDD and 128 MB RAM, RH7 machine (dual PIII and new mysql) has SCSI RAID and 512 MB RAM, same with FreeBSD server. Some RAID levels are slower than normal disks, because they concentrate on redundancy and not on speed. Which RAID level do you use? And how big is your table in bytes (not rows). Little more than 12 MB Okay, that means, memory is no problem. And disks shouldn't be a problem either, because the table fits into memory. (Sorry, don't remember the test case anymore. Did it contain INSERTs/UPDATEs?) I would start with comparing the output of mysqladmin variables Already did this. Machines with new mysql have ALL bigger values (key-buffer, sort-buffer etc) than this old mysql. And we tested them with same values too... nothing, still alot slower :( Well. Next, compare the output of EXPLAIN for all machines. In any case, it should be the same for the same MySQL versions. If it differs for any of the test cases, this could cause the speed difference. Yes, they are same with same versions of MySQL. This implies, they are not the same for different versions of MySQL? Then, as I said, this is the probably cause for the speed difference. Could you post the output of both, please? Bye, Benjamin. - 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
speed problems
Hello, I encountered following problem: when using older mysql (3.22.23 vs 3.23.33) then older is about 4-6 times faster on the same machine. What could be the problem? Sytem is Debian 2.2r2, older mysql was from .deb but newer i built myself. The query looks like this: SELECT f.foto_id, f.imgname, f.path FROM foto f, indeks k1, indeks k2 WHERE f.foto_id = k1.foto_id AND k1.word = 'mati' AND f.foto_id = k2.foto_id AND k2.word = 'kose' GROUP BY f.foto_id; Where foto has ~8000 rows and indeks has ~15 rows (words) and result has 1266 rows. Here are some testing results on different machines and op. systems: 3.22.32- 3 sec (Debian, PIII 600) 3.22.32- 6 sec (Debian, Cel 333A) 3.23.32- 24 sec (RH7, Dual PIII 500) 3.23.33- 36 sec (Debian, Cel 333A) 3.23.29a-gamma - 71 sec (FreeBSD, PIII 550) 3.23.29a-gamma - 90 sec (FreeBSD, Cel 333A) And what is most amazing... It's SOO slow on BSD. I wonder why? Or are there any ways to make this query faster? Sometimes there will be even 3 or more words (here only 'mati' and 'kose'). Rgds, Viljo - 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
