How do you classify a query as being server intensive?

[Lea Verou]

I have this question for a long time...
How can I understand whether a query that I am planning to write is server intensive or not?
Does it depend on the table size?
On the where statement of the query?
On if it only counts and not selects?
On what?

Thanks in advance

[Marco van Herwaarden]

I would say in order of importance:
- WHERE/JOIN
- ORDER BY
- Amount of data

Most important is to avoid full table scans on larger tables/joins.

Easiest is to turn on debug mode, and examine the query times and the explain queries.

[Lea Verou]

So, of I don't put a where statement and exclude the data I don't need by php code it would be lighter?
Same for ORDER BY, if I don't put that in the query and then sort the results when they are in an array the script would be lighter?
Does it count whether the query selects the whole fields or counts them (with the COUNT(*) in the query) instead?

[Andreas]

- Queries that don't use indexes
- Queries than scan a large amount of rows
- Queries that cause filesorts
- Queries that involve multiple tables

[Lea Verou]

What is an index exactly? vbulletin tables have indexes? The table that stores the post texts? I am thinking of making a smilie usage statistics mod, but I'm afraid the queries will be very intensive :nervous:
If the table has an index how should I use it in my query to make it less intensive?

Oh and btw thanks Andreas and Marco for your replies

[Marco van Herwaarden]

Indexes are added to the tables. You don't 'use' them in your queries (although you can give hints). MySQL will automatically try to use the most efficient access method.

If you are handling large amounts of rows, it will hardly ever be more effecitive to do sorting in PHP.

An index is just what the word suggest: an index.

Compair it with a book.
If the book have a nice wordlist/index, that points you to the page where you can find some information, you can find it rather fast. If there is no index, you will have to go read all pages of the book until you have what you are looking for.