Right thanks for all the tips the 3 column index has done the job,
queries coming back in 0.7 secconds now which is just the job before
they get cached.
Don't know how I missed that one as it was abovious...i even tried
countyid and old...forgot about price..
John
--
MySQL General Mailing List
It is a property of Mysql that such a query will benefit greatly from a
composite index. So I would not consider anything else without having tried
this.
Am Thursday 11 November 2004 16:29 schrieb John Smith:
> On Thu, 2004-11-11 at 14:59, Victor Pendleton wrote:
> > If you build the composit i
On Thu, 2004-11-11 at 15:51, mos wrote:
> John,
> Create a second table (MyISAM) but this time don't use compression
> on the table.
>
> create table newtable select * from oldtable;
>
Right will run that just now, good idea...just have to avoid the wife as
no doubt it will bog the sit
On Thu, 2004-11-11 at 15:45, Jigal van Hemert wrote:
> `price` is still in the ORDER BY, so removing it only from the WHERE clause
> will not help really.
No the query I ran didn't have an order by clause (sorry if the one I
pasted did..)
> - create an INDEX on the columns in the WHERE clause _an
At 07:52 AM 11/11/2004, you wrote:
Afternoon All,
The table is read only for most of the day and will get updated (once I
get the queries down to an acceptable level) nightly...if that helps.
Any suggestions or further tips would be very helpful as its taken me
months to get my code to input the da
From: "John Smith" <[EMAIL PROTECTED]>
> On Thu, 2004-11-11 at 14:36, Philippe Poelvoorde wrote:
> > Hi,
> >
> > could you try adding a key with
> > ALTER TABLE properties ADD INDEX(countyid,old,price);
> > It could maybe help getting less rows at a time.
>
> I dropped the old and price for the whe
On Thu, 2004-11-11 at 14:59, Victor Pendleton wrote:
> If you build the composit indexes as suggested, does your performance
> improve?
Erm, do you think it would? Its just that with such a large table and it
being compressed it takes ages?
--
MySQL General Mailing List
For list archives: htt
If you build the composit indexes as suggested, does your performance
improve?
John Smith wrote:
On Thu, 2004-11-11 at 13:58, Victor Pendleton wrote:
What does the explain plan look like?
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE prope
On Thu, 2004-11-11 at 14:36, Philippe Poelvoorde wrote:
> Hi,
>
> could you try adding a key with
> ALTER TABLE properties ADD INDEX(countyid,old,price);
> It could maybe help getting less rows at a time.
I dropped the old and price for the where clause and the number of rows
scanned were the sam
On Thu, 2004-11-11 at 14:37, Andy Eastham wrote:
> Have you got a single multi-column index on countyid, price and old, or do
> you have individual indexes on each of these fields? The former would be
> much better.
Its a single column on countyid, when I ran a select and just used
countyid = in
Hi,
could you try adding a key with
ALTER TABLE properties ADD INDEX(countyid,old,price);
It could maybe help getting less rows at a time.
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/
; To: Victor Pendleton
> Cc: [EMAIL PROTECTED]
> Subject: Re: Queries taking 60 seconds+
>
> On Thu, 2004-11-11 at 13:58, Victor Pendleton wrote:
> > What does the explain plan look like?
> >
>
> id select_type table type possible_keys key key_len ref
&g
On Thu, 2004-11-11 at 13:58, Victor Pendleton wrote:
> What does the explain plan look like?
>
id select_type table type possible_keys key key_len ref rows
Extra
1 SIMPLE properties ref old,price,countyid countyid 3 const 9233 Using where;
Using filesort
The filesort I know
What does the explain plan look like?
John Smith wrote:
Afternoon All,
I have the following table structure:
CREATE TABLE properties (
id int(11) NOT NULL auto_increment,
propid varchar(14) NOT NULL default '0',
townid varchar(255) NOT NULL default '',
countyid mediumint(5) NOT NULL default '0'
14 matches
Mail list logo
