List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 18 2000 11:13am
Subject:Re: support#318: Advanced Index Tuning + Query Optimization
View as plain text  
>>>>> "Joshua" == Joshua Chamas <joshua@stripped> writes:

Joshua> Michael Widenius wrote:
>> 
>> It would be nice if you could come up some reason for what Oracle is
>> doing better in this case.  I don't think the above could be the
>> reason;  You should be able to check this by just removing the old primary
>> key and making the primary key:  (primary_col, extra_col). This should
>> use the index optimization;  If this really helps, we could some code
>> that checks if we could use some other index in the 'const' case.
>> 

Joshua> Thanks Monty, I'll try to make my case with some numbers.

Joshua> Note that the query I'll be working on is a join where
Joshua> there may be many more disk i/o's because of having
Joshua> to go to the table for very many rows instead of just 
Joshua> using the right index, that's the theory anyway... :)

>> What kind of queries are you doing?  It would be nice to know exactly
>> what operations are slower in MySQL;  We have tried to make most
>> things as fast as possible, but there is always a change that we have
>> missed something and this is what you are hitting.
>> 

Joshua> There were lots of performance tunes that I was using with Oracle 
Joshua> which were of the "use index to avoid table" type... I'm hoping 
Joshua> that this is the answer, because I'm running out of stuff to optimize!

>> On thing is that all numerical data is converted to ASCII for the
>> client.  We plan to shortly add a (optional) binary interface to the
>> MySQL data in the client and this will address this issue.
>> 
>> Anyway, are you accessing MySQL through ODBC or directly?  Are you
>> using named PIPE:s or TCP/IP?
>> 

Joshua> I'm using DBD::mysql over TCP/IP to a remote database.

Are you running this on windows?  If yes, you can also use named pipes.
Try using the hostname '.'  (I actually haven't tested named pipes
against a remote machine, but in theory this should be ok).

>> Have you tried to run the same thing on Linux?  It would be really
>> interesting of knowing if this would improve the performance?
>> (Just curious).
>> 

Joshua> I'm sure it would be much better, but I'm wed to NT for now.
Joshua> My Oracle experience recently is also on the same NT box.

Even if you are wed, it would be nice to just know the difference...
(At least for me :)

>> If you could make an additional copy of the table and run some
>> benchmarks on the queries that uses a composite key and see if you get
>> any speedup on these queries.  If you do, we will look at adding this
>> extra optimization.
>> 
>> Anyway, have you tried to sort the rows/index with myisamchk?  This
>> may also give you a big performance boost!
>> 

Joshua> I'll try anything at this point ;) Thanks for the tips, 
Joshua> and hopefully I'll be blowing the Oracle performance away
Joshua> shortly!!  I'll get back to you when I have more, over
Joshua> the support channel, since I'll be sending you some of my
Joshua> oh so private tables structures.

Regards,
Monty
Thread
support#318: Advanced Index Tuning + Query OptimizationJoshua Chamas18 Jan
  • support#318: Advanced Index Tuning + Query OptimizationMichael Widenius18 Jan
  • Re: support#318: Advanced Index Tuning + Query OptimizationJoshua Chamas18 Jan
    • Re: support#318: Advanced Index Tuning + Query OptimizationMichael Widenius18 Jan
  • Re: support#318: Advanced Index Tuning + Query OptimizationJoshua Chamas18 Jan
    • Re: support#318: Advanced Index Tuning + Query OptimizationMichael Widenius18 Jan