Hi.
On Tue, Feb 06, 2001 at 05:14:39PM +0300, [EMAIL PROTECTED] wrote:
> Hello Pelle,
>
> Tuesday, February 06, 2001, 3:49:53 PM, you wrote:
>
> PE> Hi,
>
> PE> I think I have a problem with performance.
> PE> In my Mysql db I have two tables user (U) and service (S).
> PE> I want to read execurl for services registerd on a user ordered by
> PE> priority.
>
> PE> I have made a test program in java (JDBC) that sends select like this
> PE> I pool connections snd prepared statements so I don't think its a problem
> PE> with java.
> PE> I tested it agains a single table and then I got 800/selects per second.
>
> PE> SELECT S.execurl FROM S,U WHERE U.userid='ulrika0' AND U.inmethods LIKE
> PE> '%INVITE%' AND U.serviceid=S.serviceid ORDER BY U.priority
>
> Classicerror with MySQL : you place condition for link 2 table
> (U.serviceid=S.serviceid ) after condition on values. Better way is
> join like this :
> SELECT S.execurl FROM S inner join U on U.serviceid=S.serviceid WHERE
> U.userid='ulrika0' AND U.inmethods LIKE '%INVITE%' ORDER BY U.priority;
Sorry? MySQL reorders the conditions in ON and WHERE clauses as
needed, AFAIK. I doubt that EXPLAIN on the two queries differs at all.
> And make 2 indexes on tables 1. on S - serviceid,exeurl
I doubt, that MySQL will make use of "index only" for exeurl which
doesn't seem to be a numeric type. In this case, a key on S.serviceid
(probably a PRIMARY) should be enough.
> 2. on U - serviceid,userid,inmethods,priority
Since there are no restriction on S, U will be read first. So
U.serviceid (as first field) will prevent the index from beeing used,
I think. Furthermore, LIKE '%word%' will prevent index use for the
field inmethods, therefore the indeex also cannot help the ORDER BY
clause. So it boils down to a normal index on userid.
Well, one _possible_ cause for the "speed problem" could be LIKE
'%INVITE%' which has to scan all rows for the user in U.
To the original writer: I think, 800 select per second over JDBC isn't
so bad (depending on hardware). How many often can you execute
SELECT 1;
respectively
SELECT userid FROM U WHERE userid='ulrika0' LIMIT 1;
per second?
To say more, EXPLAIN on the SELECT wouldn't be bad, and SHOW INDEX for
both involved tables.
Bye,
Benjamin.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
