Slow SQL queries

shawjames

I am having issues on my webhost for exceeding CPU limits. The cause is SQL queries that are taking too long. I have a list of them I am going to try and fix, but some I do not know what to do different:

Here is one:

Query_time: 2 Lock_time: 0 Rows_sent: 1 Rows_examined: 381234

use shawcom_db;
select sum(pprice) from ds_carts where oNum='70961' and cFlag='Y'

Anyone know why this is marked as a problem query?

Thanks!
-Shaw

Piranha

Have you indexed pprice, oNum and cFlag? Is it possible to change the query so you don't have to do sum every time? Like have a column in the table that oNum is a foreign key that keeps track of the sum and that can be incremented when you add something in ds_carts?

web_designer_iq

Yes i have user_id (Primary_Key) and i don't have forigen key, i just want to faster check.
note: any command to order users table by last_login DESC (for ever)?
thanks

shawjames

Piranha wrote:
Have you indexed pprice, oNum and cFlag? Is it possible to change the query so you don't have to do sum every time? Like have a column in the table that oNum is a foreign key that keeps track of the sum and that can be incremented when you add something in ds_carts?

I indexed these columns. What does indexing do? Any reason not to have all fields indexed?

I have another issue with slow queries, using SELECT *

select * from ds_products where pID = '32561' or pNum = '32561'

Is it bad to use * for select all? Should this be changed to a list of the field names, and all those names be indexed?

Thanks!

NogDog

Indexes speed up select queries, primarily where the indexed columns are used in "where" and/or "join" clauses. However, indexed columns slow down inserts and updates to some degree as they require some additional data and processing, so you ideally only want to index those columns where it will provide a benefit.

I normally prefer listing each column to be retrieved rather than using "*" for two reasons: (1) you should only retrieve the data you need (otherwise you're using up processing time and memory/disk space for data you don't use), and (2) you then know exactly what fields are being retrieved and in what order, avoiding problems should the table structure change at some point in time.

shawjames

My main problem right now is slow queries are causing me to exceed my CPU limit on host. The frustrating thing is I have used the same queries for quite some time, and all of a sudden they are causing issues. Most likely because of higher traffic, but when I contact my host they tell me it is because the scripts are not efficient. Difficult to fix if the issue is not consistant...

Queries like this get run 1000s of times per day:

select * from ds_products where pID = '22782' or pNum = '22782'

So to speed this query up, I could try only selecting the fields that will be used and index all fields involved (except pID, which is primary).

Any other way to speed this query up? Does the "or pNum = '22782'" slow this query down?

suntra

Just a little question... have you recently repaired your tables ?

shawjames

suntra wrote:
Just a little question... have you recently repaired your tables ?

Not recently, but there is no indication that repair is needed.

What does repair do exactly, and is there any harm in repairing on a frequent basis?

MarkR

Using "select *" does not affect the number of ROWS which will be examined to return the results. It does however, increase the number of columns which are RETURNED.

If you have a large number of big columns, or are returning a very large number of rows, "SELECT *" may be significantly slower.

For a select which only returns a small number of rows, "SELECT *" will make little difference, particularly if there is nothing big in the other columns.

"SELECT *" doesn't affect the speed it FINDS the rows, only what it returns.

Any column which you're using in a where clause or join should probably be at least PART of an index. In most cases this means it will have a non-unique index on it.

Having an index on a column is independent of whether it is or isn't a foreign key (If it's a foreign key, that suggests it's likely you will use it in a join or where, in which case it should definitely be indexed).

Columns which aren't FKs often need to be indexed as well. Examples are: status, active flags etc

I'm guessing in this case, that ds_carts.oNum and ds_carts.cFlag are good candidates for having an index each, unless they already have them.

Mark