I have this SQL query: SELECT tbl1.col1, tbl2.col2 FROM tbl1, tbl2 WHERE \ tbl1.col3 = tbl2.col4 AND tbl1.col5 = '123';
(well, maybe there are more than two columns selected, and maybe a little more than two tables, but you got the idea...) The tables have 5...10 columns, every column is an integer type, and they have A LOT of rows (the total amount of space occupied by those tables on disk is 2 GB). The problem is, MySQL-3.23.46 takes forever to return from SELECT (i let it run over night, in the morning i still didn't got any results, so i killed the query). Splitting the big tables into small ones will not help, because i still want to be able to run the SELECT across _all_ data (and it's not convenient for my application). I cannot modify the structure of the tables (the application requires a certain table structure). So i wonder if there's any way to optimise MySQL so that i will be able to actually do a SELECT on those tables. Maybe tweak parameters like join_buffer_size? table_cache? Anyone has some experience with these?... What's the best way to optimize MySQL for running SELECTs on multiple large tables? This is the only thing that keeps me from deploying MySQL in production now. (Not to start a flamewar, just to acknowledge a fact: i ran the same SQL statement, with the same data set, on "some other SQL servers", and i got the result in 5 minutes, as opposed to MySQL still giving back nothing after several hours. But i want to use MySQL because i need something that can do INSERTs very fast, and MySQL is very, very good at this. The only problem is, it is slow when it comes to SELECTs on multiple large tables, and i'm not sure how to optimize it for that.) -- Florin Andrei "Engineering does not require science." - Linus Torvalds --------------------------------------------------------------------- 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
