* DeRyl > * Roger Baklund > The first thing to notice: "Using temporary"... this is to be avoided, if > possible. > > ## how is the correct way to avoid that?
Depends, in this case I think it is because of the DISTINCT. > The first table read is klientslowo based on the criteria > klientslowo.klientslowo LIKE 'sam%'. Is this a reasonable approach to > solving the query, giving your knowledge of the database structure and the > data distribution? MySQL estimates that 351 records matches this criteria, > is this close to the truth? If not, run ANALYZE TABLE. > > ## after analyze I saw: > > Table Op Msg_type Msg_text > ikt.klientslowo analyze status Table is already up to date You did not answer my questions... > You should not need the DISTINCT keyword. There is no PRIMARY KEY in the > branzaslowa table, it seems to have duplicates? > > ## it shouldn't so I'll try to select without DISTINCT The EXPLAIN output indicates that 3 rows would match in the branzaslowa table based on klientbranza.branzaid... I guess it should be only one, and if it was, the query could be up to three times faster. > You use a lot of bigint's... an unsigned int is usually sufficient, it > consumes half the disc/RAM space... this could be important for > the speed of the indexes. Optimally you would want all your > indexes to fit in RAM, i.e. the sum of the size of the indexes > should be smaller than your index buffer size (key_buffer_size). > > ## some of tables [like klient, klientslowa, klientulice, > klientmiejscowosci > etc...] uses in klientid field a numeric value which is 12,14 or > 14 characters long > so how to correct that? Your klientid column is a BIGINT. This column type is stored in a binary format, it allways consumes 8 bytes. You could keep this column in the klient table, and create a new internal id column, datatype should be unsigned int, maybe with auto_increment, and use this new column as the primary key and in any table that references the klient table. The existing klientid should have an UNIQUE index in the klient table, and be replaced by the new column in any other table. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
