>>>>> "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
