Page 1 of 1

Slow query response times

Posted: Tue Jul 02, 2002 11:57 pm
by Hebbs
I am having trouble with some queries that are becoming painfully slower as my database increases.

I am running MYSql 3.23.38 and have a total of 32 tables in my db, with some 5meg of data contained therein.

I run a main table (with 38 columns) and link to other tables when required via common keys.

I am operatinga Pentium III 450 with 128 mb ram as a server. Apache is my choice of webserver.

Whilst this setup is obviously not the best money can by, it is the best I have to work with and is still a fairly reasonable system.

My problem (as stated earlier) revolves around response speed. The main search query is taking between 10 and 30 seconds to get a response posted. This is unacceptable and is getting worse. If you believe all you read MYSql should be faster than this so its obviously something Im doing.

I have posted a copy of the query on the PHP page (this site) but have had no responses as yet.

Can anyone give me any suggestions as to what might be the problem here - or at the very least where to go ? (Be nice now!)

Yours in frustration

Hebbs

Posted: Wed Jul 03, 2002 3:03 am
by mikeq
Do you have any indexes.

Posted: Wed Jul 03, 2002 4:01 am
by Hebbs
Yes,

I have indexes on all tables, about 3 a table.

I have indexed the common and identifying numbers on all tables.

Having said this, I must admit I have never really possessed a good understanding of the indexes as they apply to a database.

Is there a rule of thumb, or what are the rules here?

Hebbs

Posted: Tue Jul 16, 2002 2:02 am
by Hebbs
OK,

For anyone else suffering similar problems, I am now in a position to offer the following couple of simple Index guides:

1. Only Index the columns mentioned in your FROM section of your query (eg ...SELECT * FROM columna.tablea, columnb.tableb etc WHERE ...)

2. Index columns involved in LEFTJOINS

3. At dos use the 'explain' command to give a siummary of your query performance. (eg mysql> explain SELECT * FROM columna.tablea, columnb.tableb etc WHERE ...;)

Particularly the last has proven invaluable and returns a table detailing the tables, their types, keys or indexes avaialable for this query, key or indexes in fact used, key length, reference, rows and extra info.

If your indexes are providing best benefit the rows result in the explain should be one, ie only one row searched.

Try this its very handy for tweaking your tables.

Hebbs