On Sun, 5 Oct 2003, Alexis da Cruz Henriques Guia wrote:
>
> Your problem is that MySQL don't use indeces in selects with 'or' (See MySQL
> manual).
If you are referring to:
http://www.mysql.com/doc/en/Searching_on_two_keys.html
then that page doesn't say mysql doesn't use indexes on selects with
or. It is, as far as I can tell, talking about the case where you have
two different keys in the OR clause. In my simplified query, I only
have one key with multiple OR values and the page says "searching on
one key with different OR parts is optimised quite well".
Granted, it isn't clear if the page is referring to one single column
or all the columns involved in one unique index so it isn't clear if the
case that they say is optimized well would be of use to me or not.
mysql certainly is using an index for the relevant queries, it is just
doing a range scan of that index, instead of individual lookups. The
range scan may well be faster if it were reading all the data from disk...
but it isn't, and never will be for my query.
> But i didn't have understand what's your problem executing individual
> selects...
> You don't need to open parallel connections. Put the UPDATE commands in a file,
> and execute them in mysql prompt like this:
>
> mysql> source fileName;
>
> (for help, type 'help' in mysql prompt)
>
> Is this your problem?
no... my problem is that it is still far too slow to do it that
way across a network given that you can only send one command to
the server at once, then you have to wait for the response. Not only is
there the network latency that adds up when you want to do thousands
of operations per second, even on 100 mbit or gigabit ethernet, but there
is the OS scheduling latency involved. When doing batch updates, passing
the update in batches to the server is nearly always going to result in
far better performance... if the server supports it properly.
Sticking multiple statements in one file and passing it to the
mysql command line client doesn't change the problem in any way. In any
case, I can't do any of this by writing commands to a file and
loading them using the mysql client, I need to do it using JDBC. I
have no problem executing all my updates sequentially except for the
fact that it is far too slow.
But thanks for the response...
>
> ;)
> Alexis
>
> Quoting Marc Slemko <[EMAIL PROTECTED]>:
>
> > If I do a query such as:
> >
> > SELECT * from foo where fooid = 10 or fooid = 20 or fooid = 03 ...
> >
> > with a total of around 1900 "or fooid =" parts on a given table with 500k
> > rows, it takes about four times longer than doing 1900 separate
> > queries in the form:
> >
> > SELECT * from foo where fooid = 10
> >
> > fooid is the primary key.
> >
> > My real goal is to do updates in a similar way, they have the same issue.
> > And my real goal is actually doing the same thing where instead of
> > "fooid=10" I have a multicolumn primary key and use "(keycol1=10 and
> > keycol2=2
> > and ...)" My examples here are the simplest case I can find that exhibit
> > the issue.
> >
> > Explain plan on the select shows:
> >
> > table type possible_keys key key_len ref rows Extra
> > adsummary range PRIMARY PRIMARY 31 NULL 1915 Using where
> >
> > so it is doing a range scan in some manner. Given that the rows
> > I'm updating will normally be cached in memory, is there anything I
> > can do to force it to just do repeated index lookups like the
> > individual queries are doing? The reason I don't want to use
> > individual queries is because my performance is then bottlenecked
> > by latency, and when trying to update thousands of rows a second
> > that doesn't work out. Just opening multiple connections and
> > doing them in parallel is a problem because of where the updates are
> > coming from and the fact that they have to be done inside a transaction
> > so other connections would just end up blocked anyway.
> >
> > Currently running 4.0.15a.
> >
> > I'm trying to avoid going to 4.1 at this point, but it does look
> > like the INSERT ... ON DUPLICATE KEY UPDATE ... syntax may do
> > individual index lookups the way I want... I haven't verified
> > that yet though.
> >
> > Any suggestions are greatly appreciated.
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/[EMAIL PROTECTED]
> >
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
