Hi Florian, ----- Original Message ----- From: "Florin Andrei" <[EMAIL PROTECTED]>
> 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). Well, you have to realise that the server can only use a one index per table (for obvious reasons). So, the first question is, which fields are indexed, and which index does the server use for this query. To find out the latter, as well as find out in which order the server joins the tables, use EXPLAIN .... with your query. The way you'll want to set up a join (applies to any SQL server) is to limit the number of rows as fast as possible. For instance, if a table has a male/female column, putting an index on that will only cut the number of rows to be searched by half, so there'll still be heaps of rows that need to be checked. In a select join, that might not be very efficient. So, first think about these things, to set up your indexes and the query properly. It IS possible to tweak the server for a particular query, if you find that the optimiser isn't picking the best index. If you use STRAIGHT_JOIN instead of the commas, you can specify the join order. And you can also direct the server to use (or ignore) specific indexes (see manual for details). Those are just a few things. > 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). You can actually split a table into multiple identical ones, by using MERGE tables (see manual). But that just makes managing them easier (and inserts into one table faster), it wouldn't speed up your SELECT. > 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? Yes, server settings are important, and the 'right' settings depend on your system (amount of RAM, etc) as well as on your database and the type of queries that you run most often. There is no magic wand, I'm afraid. But... you may find it advantagious to do a MySQL training course (www.mysql.com/training/). Optimisation is a very important part of the course material, in this message I've just touched briefly on a few little items. > 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.) I'm confident that the MySQL server can do it just as fast, if not faster. Regards, Arjen. -- MySQL Training Worldwide, http://www.mysql.com/training/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Mr. Arjen G. Lentz <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia <___/ www.mysql.com --------------------------------------------------------------------- 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
