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

Are the fields you are using in the WHERE clauses indexed? This could help.
How slow is "rather slow"? If the query is forcing a table scan, it will be
slow an almost any machine with that amount of data. The types of conditions
you have shown in the WHERE clause will be helped by good indexing provided
the BETWEEN conditions do not include too many rows. The nature of the
fields also matters. If they are say, 4 byte integers, they should be fast.
If they are long text strings they will be slower.

John Bonnett

-----Original Message-----
From: Ferhat BINGOL [mailto:s021183@xxxxxxxxxxxxxx]
Sent: Sunday, 23 February 2003 12:52 AM
To: win32@xxxxxxxxxxxxxxx
Cc: mysql@xxxxxxxxxxxxxxx
Subject: big table, slow queries...???


Hi,

I am new at this group. Nowadays I am using PHP/MySQL more than ever. I have
a problem with making queries on my huge database (totally more than 20
million rows and 9 GB).

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)
....

(Where statement is longer than this)


and so on. I think you understand the structure I use for making queries.
But it is too slow. I mean I was expecting to be faster.

Can you advice me a different kind of query string? I read some articles
about using JOIN, GROUP or so on. But I am little bit confused about it.

What is the best way to make queries on big table?

Thanks,
FERHAT


P.s : I am using MySQL 3.23 and PHP/Apache on a Windows XP machine. 386 MB
RAM with Pentium3 350.



Ferhat


---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"; before
posting. To request this thread, e-mail win32-thread10610@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.