Problem of performance with large tables

[prothee]

Hi all,

I'm under jboss3.26
I've got an entity bean mapped to a table with 33M of records.
I've done some finders, some are very quick, other very slow...

for example: (in jbossql)

select object(o) from TheEntity as o where o.name like ?1 ORDER BY o.name OFFSET ?2 LIMIT ?3

with a parameter like "toto%" it's really quick
with a parameter like "%toto%" it's really slow over 10s
-> it's like if all the results were loads without limit clause

I tried read ahead strategy but with no significant result

If someone have an idea....

Thanks

Arnaud

[fbiaggi]

Hi,
you may install a tool like P6Spy and see how long take the SQL statement to execute.

[lviz]

hi prothee

afaik (with oracle db)

a like with abd% uses the index
a like with %abd% drops the index and does a full table scan.

cheers
L

[prothee]

Hi all,

Little precision, i'm using mysql 4.1
ok i will install, p6spy to see the time...
but there is anything else i can do to optimise the container?

Thanks

Arnaud

[lviz]

hi again ;)

the index thing (like %abd%, drops the index) is the same for mysql !


http://dev.mysql.com/doc/mysql/en/mysql-indexes.html

cheers
L