My Simple query takes 30 min to run [message #424272] |
Thu, 01 October 2009 04:10  |
dheeraj007
Messages: 1 Registered: October 2009 Location: Japan
|
Junior Member |
|
|
Hi
I have a table with three fields.
id,attr_name,attr_value.
i have an index on id and attr_name.
there are 500 million rows in the table.
where i run the follow query ti takes 30min. how can i speed it up
select * from table where attr_name ='ad_date' and attr_value ='29/09/09'
this query in my database should return no records..but it does a table access scan...which takes about 30min to return no rows.
Thanks
|
|
|
|
Re: My Simple query takes 30 min to run [message #424280 is a reply to message #424272] |
Thu, 01 October 2009 04:22   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'm guessing that your index is in (id,attr_name), in which case it is of almost no use for any query that isn't searching by id.
Lacking an index to search the table by, it will do a full table scan - in this case, it's reading 500,000,000 rows in 30 minutes, which means it's reading about 278,000 rows a second.
That's pretty good performance in my book.
You should be able to improve the performance of this query by adding an index on (attr_name,attr_value), or (if you have a large number of values of attr_name) then you might get away with an index just on attr_name
|
|
|
|