help optimizing a query

takes too long to run

[omoutop]

Well i need to execute a quey in a script, but the results take about 108sec to appear in page.

The database is mysql, and when i execute the query direct in sql window (through phpmyadmin) it takes about 100 sec to show results.

Database and table descriptions:
table1: about 3 million records in 7 colums
table2: 1500 records in 90 colums
table3: 440 records in 20 colums

The query:
SELECT b.field1, b.field2, c.field3
FROM table1 a, table2 b, table3 c
WHERE a.field1 = b.field1
AND a.field4 BETWEEN 'value1' AND 'value2'
AND b.field5 = c.field5
GROUP BY a.field1
ORDER BY b.field2

Notes:
field1: INT(11) (unique id in table2, autoincreased)
field2: VARCHAR(120)
field3: VARCHAR(50)
field4: INT(11)
field5: INT(11) (unique id in table3, autoincreased)

Final query gives 1100 results

Any help/suggestion is appreciated

[phranque]

generally you should have an index on columns used in ORDER BY, GROUP BY and WHERE clauses.
i would try indexing b.field2 first and see what happens...

[omoutop]

Indexing the field2, doesnt seem to affect anything.

ALTER TABLE table2 ADD INDEX(field2(5));
After that, I run Analyze, Reapir and Optimize on table 2
After, i run the script again.... 5 secs more.

Not only it didnt reduce time, it increased it also.

I will try to index some other field that it is used in this query and see what will happen

Also, using EXPLAIN in my query i got the following:

table type possible_keys key key_len ref rows Extra
a ALL NULL NULL NULL NULL 2780264 Using where; Using temporary; Using filesort
b eq_ref PRIMARY PRIMARY 4 a.hotel_id 1 Using where
c eq_ref PRIMARY PRIMARY 4 b.island_id 1 Using where

[aspdaddy]

see if this helps - the group by doesnt do anythin cause your not aggregating so its taken out, the beteewn is optimised as two equal to's, and the join is optimsed

SELECT b.field1, b.field2, c.field3
FROM ((table1 a inner join table2 b
on a.field1 = b.field1 )
inner join (table3 c on b.field5 = c.field5))

where ((a.field4 ='value1') or (a.field4 = 'value2'))

ORDER BY b.field2 desc

btw - You also indexed the wrong column, you need to index a.field4 to speed this up.

[edited by: aspdaddy at 4:39 pm (utc) on June 25, 2007]

[stajer]

phranque---

what about index's on the column's used in the joins?

[phranque]

what about index's on the column's used in the joins?

those were where clauses when i first responded.
any column used for sorting, searching, grouping or joining can benefit from indexing during a select.
the trick is balancing the load and storage from maintaining the index during insert/update vs the speed gained by having the index handy when you select.
that's why i qualified first by starting with "in general...".

[phranque]

omoutop:

After, i run the script again.... 5 secs more.

Not only it didnt reduce time, it increased it also.

it probably didn't help but i wouldn't necessarily consider <5% increase significant.
it could be normal variance.
was there any other load on the system at the time?
was it a statistically significant sample size? (how many tests were run before/after?)

[omoutop]

well thanks guys.... aspdaddy you were correct.
After indexing the field4, i had a dramatical increase in speed (from 98-105 secs to 29-36 secs)

I run the script 5 times per hour, for 3 hours, to have it executed under different server load - always gives me same speed results.

I guess this can't be improved any further - so thank you all for your help.

Time to start some serious reading on indexing :)

[LBmtb]

In your explains: "Using where; Using temporary; Using filesort"

Note that using temporary and using filesort can be potential bottlenecks. Read on it: [dev.mysql.com...]