Hello everyone!
Like many others I have "plunged" into MySQL like a camel in a swwiming pool at 40C degrees outside! I'm not sorry! I love it's speed, ease of use, supplements to ANSI92 standard, but...
But I just don't get THIS! Here's the story! TRUE FACTS!
2 databases - 1 is "traffic" - 2 is "customer"
First - 2.5 mil rows, 9 columns, 2 coumns are needed for the following query: the 1st is named "ammount" and contains numbers (ammounts of traffic) and is an "int(6)" . the 2nd contains phone numbers is called "phonum" and is "varchar(20)".
Second - 80.000 rows 5 columns and 1 column is needed for the query: is called "number" and contains phone numbers as "varchar(15)".
The query:
"select sum(ammount) from traffic,customer where phonum=number;"
The problem! BIG!
This damn query takes:
- 46 seconds on a lazy VisualFoxPro select (PIII 750/256Mb RAM, 20Gb IDE Drive).
- 2 minutes and 34 seconds on RH 6.2 SMP / MySQL 3.23.41 on a Dual PIII/1GHz-1Gb RAM-36 SCSI Raid 0,1 array!!!
How come???
Are there hidden optimizing tricks to make the bitterly defeated MySQL at least reach 1 minute for this query?
Must I add runtime config cheats to make it work faster!
I felt horrible all day thinking of how could a "pseudo-database" like MS VisFoxPro 6.0 win on concatenated queries against MySQL!
I may also tell you that damn FoxPro performed better even when doing a single simple select on the 2.5 mil row table (eg: "select sum(ammount) from traffic where phonum='34564565';")
I already heard others people complaining about MySQL SELECT query speed on quite large tables (>2 mil rows). Is this true? Is this the end point of performance for a GPL database?
Please do answer! Any comments will be apreciated!
Like many others I have "plunged" into MySQL like a camel in a swwiming pool at 40C degrees outside! I'm not sorry! I love it's speed, ease of use, supplements to ANSI92 standard, but...
But I just don't get THIS! Here's the story! TRUE FACTS!
2 databases - 1 is "traffic" - 2 is "customer"
First - 2.5 mil rows, 9 columns, 2 coumns are needed for the following query: the 1st is named "ammount" and contains numbers (ammounts of traffic) and is an "int(6)" . the 2nd contains phone numbers is called "phonum" and is "varchar(20)".
Second - 80.000 rows 5 columns and 1 column is needed for the query: is called "number" and contains phone numbers as "varchar(15)".
The query:
"select sum(ammount) from traffic,customer where phonum=number;"
The problem! BIG!
This damn query takes:
- 46 seconds on a lazy VisualFoxPro select (PIII 750/256Mb RAM, 20Gb IDE Drive).
- 2 minutes and 34 seconds on RH 6.2 SMP / MySQL 3.23.41 on a Dual PIII/1GHz-1Gb RAM-36 SCSI Raid 0,1 array!!!
How come???
Are there hidden optimizing tricks to make the bitterly defeated MySQL at least reach 1 minute for this query?
Must I add runtime config cheats to make it work faster!
I felt horrible all day thinking of how could a "pseudo-database" like MS VisFoxPro 6.0 win on concatenated queries against MySQL!
I may also tell you that damn FoxPro performed better even when doing a single simple select on the 2.5 mil row table (eg: "select sum(ammount) from traffic where phonum='34564565';")
I already heard others people complaining about MySQL SELECT query speed on quite large tables (>2 mil rows). Is this true? Is this the end point of performance for a GPL database?
Please do answer! Any comments will be apreciated!