>
> AND Substring(a.mob, 1, 4) = b.mob_series
>
There's what is probably the major problem with your query: your join
condition. Indices (you *do* have them on your join fields, don't you ?)
only work on the entire field you've indexed.
Function indices are not supported in MySQL, so you'll
using Execution plan:
++++-+---++-+---+-+-
---+
| id | select_type| table | type|
possible_keys | key| key
+---++-+--+-+---+-+--++--++-+
| Table | Non_unique | Key_name| Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |
+-
the dataset,
then MySQL has it right in this case.
- Original Message -
From: Brent Baisley <[EMAIL PROTECTED]>
To: bowen <[EMAIL PROTECTED]>, mysql@lists.mysql.com
Sent: Monday, October 9, 2006 10:52:47 AM GMT-0500 US/Eastern
Subject: Re: How to speed up query of indexed colu
of transferring the results.
- Original Message -
From: "bowen" <[EMAIL PROTECTED]>
To:
Sent: Monday, October 09, 2006 8:54 AM
Subject: How to speed up query of indexed column with 5M rows?
How to speed up query of indexed column with 5M rows?
I have a table with more than
: Monday, October 9, 2006 9:18:01 AM GMT-0500 US/Eastern
Subject: Re: How to speed up query of indexed column with 5M rows?
Dan Buettner wrote:
> bowen -
>
> Right now, it appears your performance hinges on I/O to the disk drive.
>
> The reason you are seeing fast performance when query
.
>
> Dan
>
> On 10/9/06, bowen <[EMAIL PROTECTED]> wrote:
>> How to speed up query of indexed column with 5M rows?
>>
>> I have a table with more than 5M rows. (400M .MYD 430M .MYI).
>>
>> It took 27 seconds to do a common select...where... in the ind
consistent.
I'd recommend doing both if possible.
Dan
On 10/9/06, bowen <[EMAIL PROTECTED]> wrote:
How to speed up query of indexed column with 5M rows?
I have a table with more than 5M rows. (400M .MYD 430M .MYI).
It took 27 seconds to do a common select...where... in the index col
How to speed up query of indexed column with 5M rows?
I have a table with more than 5M rows. (400M .MYD 430M .MYI).
It took 27 seconds to do a common select...where... in the index column.
I can not bear the long run.
Vmstat show that system was bounded by IO busy.(Always more than 13000
bi/s
It should then be very fast because it doesn't need to go to the data
file -- as EXPLAIN will show with "Using index."
Hope that helps.
Matt
- Original Message -
From: "Ganbold"
Sent: Friday, October 17, 2003 4:14 AM
Subject: Re: How to speed up query?
Egor,
Result of explain:
mysql> explain select sum(size) from message where uid='2945';
+-+--+---+--+-+---+--+-+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-+--+---+--+-
Ganbold <[EMAIL PROTECTED]> wrote:
>
> I'm having some trouble running one query. I'm using FreeBSD 4.8 with
> linuxthread enabled mysql-4.0.14.
> Server has 1GB ram and SCSI hard disk.
>
> I need to get size of email message which is stored in MyISAM table.
>
> The problematic query is:
>
> s
I think, if I understand this right, that the problem is the
int(1) which limits the index to only the first digit,
try to change that to int(11) or something like that, and see if that cures it.
Kelley
Ganbold wrote:
> Hi,
>
> I'm having some trouble running one query. I'm using FreeBSD 4.8 wi
Hi,
I'm having some trouble running one query. I'm using FreeBSD 4.8 with
linuxthread enabled mysql-4.0.14.
Server has 1GB ram and SCSI hard disk.
I need to get size of email message which is stored in MyISAM table.
The problematic query is:
select sum(size) from message where uid='2945';
---
Sir, LEFT JOINs rule out the use of indices on the join column in the
left table, making LEFT JOINs inherently slow. Try putting indices on
the columns used in your WHERE and ORDER BY clauses. I'm not
promising it will help, but it is recommended in Paul's book.
Bob Hall
>Hi folks,
>
>
>after
Hi folks,
after having carefully tuned all statements within a larger web based
application rewriting SQL statements and optimizing them by adding indices,
there are still two statements left, which are awkward concerning
performance. Maybe I am just too blind to see how to get them working
fast
16 matches
Mail list logo
