>>>>> "Joshua" == Joshua Chamas <joshua@stripped> writes:
Joshua> Monty & Crew,
Joshua> MySQL query optimization is awesome, but could be a little better,
Joshua> in fact this may be a place where more "advanced" dbs like Oracle
Joshua> beat you out!
Joshua> In a bit, what I need is for queries and joins against tables
Joshua> with primary/unique keys to sometimes use other indexes when
Joshua> extras columns are being queried for or joined on. Use of indices
Joshua> in this way is meant to short circuit the query so that it does not
Joshua> have to touch the table data, everything is already in the index.
Joshua> I know that MySQL already does this, sometimes, but does not
Joshua> if the where ='s a UNIQUE or PRIMARY key.
If the index can only be resolved with one key, MySQL should only use
the index tree.
Joshua> As a simple illustration, take the table:
Joshua> create table illus (
Joshua> primary_col int PRIMARY KEY,
Joshua> extra_col int,
Joshua> INDEX i_illus_primary_col_extra_col (primary_col, extra_col)
Joshua> Now ideally if I were to issue a
Joshua> select extra_col from illus where primary_col = ?
Joshua> what would happen is just the index i_illus_primary_col_extra_col
Joshua> would be accessed for the data, but what is happening, as explain
Joshua> tells me, is that because there is a PRIMARY key that can be
Joshua> used, the query uses it with priority "const". Thus my nice
Joshua> little index is useless!
Const is a special case; This is resolved very early in the select
optimizer. On the other hand, the const will just use one disk read
for this table for the whole query so this isn't really that bad.
Joshua> BTW, I already ran myisamchk -a on my indices, and though
Joshua> performance in general improved some, this particular
Joshua> behavior did not change.
Joshua> In my app, there is one critical query that would well use this
Joshua> kind of optimizing, and at least a couple others that
Joshua> could use it too.
Do you really think this will give you any notable improvement? I
recently added some code to save a disk access in the case when MySQL
notices that a key in a 'ref' changed value and when I run the
benchmarks on this I didn't even get a 1 % speedup :(
Joshua> I have finished porting my app from Oracle to MySQL, and to my
Joshua> surprise found MySQL to be a just a little slower, where earlier
Joshua> small benchmarks showed it to be near twice as fast. My
Joshua> supposition here is that certain optimizations like this would
Joshua> help my application scale as well or better on MySQL as it did
Joshua> on Oracle.
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> Note that my app is CPU bound, and I/O usage is much better than
Joshua> it was on Oracle. It seems that my WinNT OS does a fine job
Joshua> buffering the data files, and the key buffer is only missing
Joshua> some .1% of the time.
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.
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?
Have you tried to run the same thing on Linux? It would be really
interesting of knowing if this would improve the performance?
Joshua> I am thinking a potential work around here will be to make
Joshua> the PRIMARY key a composite key that I want to cheat
Joshua> queries with, and subsequently make a UNIQUE index on the
Joshua> real primary key for integrity sake. I don't know if this
Joshua> will work, and will only do this if I am desperate as it
Joshua> may create other slow downs instead, because of the
Joshua> unique testing upon insert + update.
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
Anyway, have you tried to sort the rows/index with myisamchk? This
may also give you a big performance boost!
Joshua> So what can be done here ? Thank you.