Slow query response times

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Hebbs
Forum Commoner
Posts: 43
Joined: Mon Apr 22, 2002 9:34 pm
Location: Perth, Western Australia

Slow query response times

Post 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
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

Do you have any indexes.
Hebbs
Forum Commoner
Posts: 43
Joined: Mon Apr 22, 2002 9:34 pm
Location: Perth, Western Australia

Post 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
Hebbs
Forum Commoner
Posts: 43
Joined: Mon Apr 22, 2002 9:34 pm
Location: Perth, Western Australia

Post 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
Post Reply