RE : big table, slow queries...???

May be it's slow because in the WHERE clause you don't precise any
relation whith table4 and table5. In this case you're reading all their
contents.

B LEFEUVRE
France

-----Message d'origine-----
De : Petr Vileta [mailto:petr@xxxxxxxxxxxxx] 
Envoyé : dimanche 23 février 2003 04:47
À : win32@xxxxxxxxxxxxxxx
Objet : Re: big table, slow queries...???


> The main problem is it is rather slow. For example I am making a query
which
> is checking the 5 biggest tables for, from 2 "where" options to 20. I 
> am using something like that;
>
> SELECT
>     table1.field1,
>     table1.field2,
>     table1.field3,
>     table2.field2,
>     table3.field2,
>     table4.field1,
>     table5.field1,
>     table5.field2
> FROM
>     table1,
>     table2,
>     table3,
>     table4,
>     table5
> WHERE
>     (table1.field1=table2.field1) AND
>     (table3.field4 BETWEEN case1 AND case2)
> ....
Test to create more indexes for tables. If you can use
"table1.field1=table2.field1" in where clause is better if exist some
index for table1.field1 and table2.field1 too. Example: I have table1
(filed1, field2, ... field5) and table2 (field1, ...field5) and I can
using queries with "where table1.field1=table2.field1" clause. In this
case I must create index for both field1, eg. "create index index1 on
table1 (field1)" and "create index index1 on table2 (field1)". After you
create indexes your querie will be more quickly. I use 5, 10, 20 indexes
for 1 table basicaly. Write some simple query for two tables and compare
time consumption with and without indexes. Indexes is the way.

P.S. Look to manual for how the MySQL use indexes.

Petr Vileta, Czech Republic
http://www.practisoft.cz



---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html";
before posting. To request this thread, e-mail
win32-thread10605@xxxxxxxxxxxxxxx

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it, e-mail
win32-unsubscribe@xxxxxxxxxxxxxxx instead.




---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"; before
posting. To request this thread, e-mail win32-thread10607@xxxxxxxxxxxxxxx

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail win32-unsubscribe@xxxxxxxxxxxxxxx instead.