Re: Heavy task on mysql by AJAX Pagination?

[Guelphdad Lake]

Indexes are good in speeding up SELECTs.
They do slow down INSERT and UPDATEs.

Figure out the queries you are more likely to run on your tables before adding indexes. Again the performance is a tradeoff.

A multi column index is good in some cases (if you are searching cars for Makes and Models then you may have an index across both columns because you are likely to search both columns in a regular search against that table).

As an example of why indexes are good. I have two phone books. I'll have one with index on City and Last Name. Yours has no indexes. There are 100,000 names in the book.

Let's both look up Charlie Brown in Palookaville.

My search will quickly look up the city and then by last name.

I'll have to search through all the Browns in Palookaville of course but i've narrowed the search down pretty quickly.

you will have to open the phone book at page 1 and scan every name until you find the one you want. it may be the first entry, it may be entry 98,123 which will take longer.

If i add a new row to the table though it will take longer than yours because it will need to add the name and then build the index. That amount of lost time though is more than made up by the speed up of my SELECTs.

And in the pagination example you give, why store 100,000 names if you are only publishing 100 per web page (more or less)?

---------------
Guelphdad,
Moderator
forums.mysql.com