Hi,
I think I have a problem with performance.
In my Mysql db I have two tables user (U) and service (S).
I want to read execurl for services registerd on a user ordered by
priority.
I have made a test program in java (JDBC) that sends select like this
I pool connections snd prepared statements so I don't think its a problem
with java.
I tested it agains a single table and then I got 800/selects per second.
SELECT S.execurl FROM S,U WHERE U.userid='ulrika0' AND U.inmethods LIKE
'%INVITE%' AND U.serviceid=S.serviceid ORDER BY U.priority
when the U table contains 1000 entrys I get 90 selects/sek
when the U table contains 10000 entrys I get 12 selects/sek
I have created an index on column U.userid and serviceid is primkey in S.
Is this the kind of performance one can expect from a Mysql or have I done
everything wrong. If so can anyone give me some advise on how to do it.
/Thanks Pelle
Table S
+-----------+----------+------------+------------+
| serviceid | execurl | configurl | descrurl |
+-----------+----------+------------+------------+
| 0 | EXECURL0 | CONFIGURL0 | DESCRCURL0 |
| 1 | EXECURL1 | CONFIGURL1 | DESCRCURL1 |
| 2 | EXECURL2 | CONFIGURL2 | DESCRCURL2 |
| 3 | EXECURL3 | CONFIGURL3 | DESCRCURL3 |
| 4 | EXECURL4 | CONFIGURL4 | DESCRCURL4 |
| 5 | EXECURL5 | CONFIGURL5 | DESCRCURL5 |
| 6 | EXECURL6 | CONFIGURL6 | DESCRCURL6 |
| 7 | EXECURL7 | CONFIGURL7 | DESCRCURL7 |
| 8 | EXECURL8 | CONFIGURL8 | DESCRCURL8 |
| 9 | EXECURL9 | CONFIGURL9 | DESCRCURL9 |
+-----------+----------+------------+------------+
Table U
+------------------------+-----------+----------+-----------+---------------
-----+
| userid | serviceid | priority | inmethods | outmethods
|
+------------------------+-----------+----------+-----------+---------------
-----+
| ulrika0 | 2 | 0 | INVITE |
REGISTER,SUBSCRIBE |
| ulrika0 | 1 | 2 | INVITE |
REGISTER,SUBSCRIBE |
| ulrika0 | 5 | 1 | INVITE |
REGISTER,SUBSCRIBE |
| ulrika1 | 1 | 1 | INVITE |
REGISTER,SUBSCRIBE |
| ulrika1 | 2 | 0 | INVITE |
REGISTER,SUBSCRIBE |
| ulrika1 | 7 | 2 | INVITE |
REGISTER,SUBSCRIBE |
+------------------------+-----------+----------+-----------+---------------
-----+
---------------------------------------------------------------------
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
