Hi Gavin,Thanks very much, I'll implement as many of your suggestions as
possible. The varchar(255)'s are inexcusable and I feel suitably ashamed :)
The queries were generated by ActiveRecord (an ORM library for Ruby),
although even if I had written them myself they would probably not be much
bette
Hi Ciaran,
So I think there's a couple things going on:
1. The explain plan for your "slow" query looks wrong, such as mysql is
confused. It's possible your index statistics are incorrect. Try ANALYZE
TABLE on listings and addresses.
I think a sure way to fix it is to add STRAIGHT_JOIN to f
[EMAIL PROTECTED] wrote:
Yes, a ranged query should respond faster than a negation. In some cases
you can seriously improve query performance for a negation query if you
split it into two range queries unioned together. Here is a pseudo
example:
This query should be slow due to the table scan
Robert DiFalco wrote:
> In a previous database engine I was using an IN was more optimal than a
> <>. So, for example:
>
> SELECT * FROM table WHERE table.type IN (1,2,3);
>
> Where the possible values of type are 0-3, was appreciably faster than:
>
> SELECT * FROM table WHERE table.type <
ql.com
Subject: RE: Query Optimization Question
Yes, a ranged query should respond faster than a negation. In some cases
you can seriously improve query performance for a negation query if you
split it into two range queries unioned together. Here is a pseudo
example:
This query should be slow
>
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 13, 2006 6:37 AM
> To: Robert DiFalco
> Cc: mysql@lists.mysql.com
> Subject: Re: Query Optimization Question
>
>
>
>
> "Robert DiFalco" <[EMAIL PROTECTED]>
Shawn,
Any performance gains for specifying "type > 0" than "type <> 0" ?
R.
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, March 13, 2006 6:37 AM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re
"Robert DiFalco" <[EMAIL PROTECTED]> wrote on 03/11/2006 12:43:43 PM:
> In a previous database engine I was using an IN was more optimal than a
> <>. So, for example:
>
> SELECT * FROM table WHERE table.type IN (1,2,3);
>
> Where the possible values of type are 0-3, was appreciably faster th
Robert DiFalco wrote:
In a previous database engine I was using an IN was more optimal than a
<>. So, for example:
SELECT * FROM table WHERE table.type IN (1,2,3);
Where the possible values of type are 0-3, was appreciably faster than:
SELECT * FROM table WHERE table.type <> 0;
I
When you don't have subselects, you have two options: temporary tables or
JOINed queries.In your case, I think the temporary table is the better way
to go.
I would also eliminate the ABS() check so that I can compare values
directly against the index. I know the math is correct your way but th
Gerald Taylor wrote:
Query optimization question
I am selecting from a single table but it has a lot of rows and it
has a very involved calculation. What I really want to do is
is FIRST restrict the number of rows so that the big calculation is only
performed on the ones that are within 3 degr
11 matches
Mail list logo
