More about Table optimization and indexs

Anon

The following is the table structure that I'm using, only one field, 'codice' is Primary key and not null, all others may have or may not have data inside.

There are 4 SELECT queries inside, and all of them are pointing to a defined field name.
They are needed in order to put data inside combo boxes. Table has about 2500 records of text using only smallint, varchar and only one text field type.

What can I do to increase performance ?

thank you for your help!

Fabio

Anon

Use UNSIGNED INTS where possible to improve the maxiumum size of your fields (won't increase performance).
Set as an index ANY field that is part of any WHERE clause, GROUP BY clause or ORDER BY clause in your SQL.

Anon

Set as an index ANY field that is part of any
WHERE clause, GROUP BY clause or ORDER BY clause in your SQL

This is not a panacea to be blindly applied. Yes, absolutely, it can
speed up queries. Just as absolutely, however, it slows down the
speed of inserts and updates, due to the need to update the
indexes. Sometimes, you just have to try it both ways and test...

Anon

Thanks for the answers, I will make some tryies later.

One consideration: I don't know if you have tryed the URL I give, what I see, is that yes, the first page is very slow, but the results page, seems fast as slow: it returns immediately the number of records that respect the search criteria and than takes a while to download data.

What do you think about ?

May be that the combo box and the HTML architecture that are slowing down ?

I have 2500 records yes, but I was thinking that we could make something better using MySQL who claims to support DB bigger than 100.000 records ...

Thank you

Anon

I looked at the URL you gave and indeed it took a lot of time to construct the search filter selection page with all the SELECT boxes.

I have pages similar to this in my application, with multiple SELECT boxes. haven't gotten yet to the stage where a table used for SELECT will have 2500 rows, but shouldn't be a problem if the right index is created.

A couple of things that come to mind:

have string resources which are relatively constant grouped into a single table. I use this way and read all the resources table once into an array of objects and then fetch the strings and codes for the type of resource needed for each select box from that array.
have indexes that include exactly what you want: it should be in the right order for your WHERE clause, but also note that it's better to have fields with greater cardinality so that the query optimizer selects this index for use. Also try to make the index include all the data you need so after fetching the index page(s) there's no need for yet another fetch of a page from the table.
Check your hardware configuration: is the server host fast enough for the job ? does it have enough memory ? Is the DBMS configured to make use of that memory ?
In some DBMS, there's the option of pinning an index or table into the memory. If this is a hotspot for your application (i.e. accessed all the time), than maybe you should consider this option.

Hope this helps
Yaron Yogev

Anon

One excellent resource to learn from about performance tuning:
http://manuals.sybase.com/onlinebooks/group-as/asg1200e/aseperf

(adpative server generic collection, performance tuning guide)

Anon

Thank you for the answers, I have make many tries, but speed is still low :-(

I have checked some other search engines like Amazon and so on and I see that no one is using combo boxes with the list of films or actors...

I think to understand the reason and I think I will move in this direction...

Thank you

Fabio