I have a very simple table, with one field of type double in it. I want to search for values in this column, so, I indexed it. But MySQL does not seem to use the index. If I query for a exact value, it does: ie: where col=5.3435 I get the one record which matches. But if I type col>5, I get the correct results (obviously!) but the index is no longer used. As i want to query for values, for instance, between 5 and 6, and I expect a lot of records on this table, and a lot of queries as well, this is worrisome... How can I get MySQL to use the index?
Interesting: if I sort the table on the column, it does not use the index either...
> I have a very simple table, with one field of type double in it. I > want to search for values in this column, so, I indexed it. But MySQL > does not seem to use the index. If I query for a exact value, it does: > ie: where col=5.3435 I get the one record which matches. But if I type > col>5, I get the correct results (obviously!) but the index is no > longer used. As i want to query for values, for instance, between 5 > and 6, and I expect a lot of records on this table, and a lot of > queries as well, this is worrisome... How can I get MySQL to use the > index?
> Interesting: if I sort the table on the column, it does not use the > index either...
> On 7 Nov, 14:18, profke <pwieg...@gmail.com> wrote:
> > Hi everybody,
> > I have a very simple table, with one field of type double in it. I > > want to search for values in this column, so, I indexed it. But MySQL > > does not seem to use the index. If I query for a exact value, it does: > > ie: where col=5.3435 I get the one record which matches. But if I type > > col>5, I get the correct results (obviously!) but the index is no > > longer used. As i want to query for values, for instance, between 5 > > and 6, and I expect a lot of records on this table, and a lot of > > queries as well, this is worrisome... How can I get MySQL to use the > > index?
> > Interesting: if I sort the table on the column, it does not use the > > index either...
> On 7 nov, 15:40, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> > On 7 Nov, 14:18, profke <pwieg...@gmail.com> wrote:
> > > Hi everybody,
> > > I have a very simple table, with one field of type double in it. I > > > want to search for values in this column, so, I indexed it. But MySQL > > > does not seem to use the index. If I query for a exact value, it does: > > > ie: where col=5.3435 I get the one record which matches. But if I type > > > col>5, I get the correct results (obviously!) but the index is no > > > longer used. As i want to query for values, for instance, between 5 > > > and 6, and I expect a lot of records on this table, and a lot of > > > queries as well, this is worrisome... How can I get MySQL to use the > > > index?
> > > Interesting: if I sort the table on the column, it does not use the > > > index either...
> > > Anybody got any ideas?
> > > thanks!
> > > Paul
> > What does an explain say?
> Well, it simply says: possible_keys: null
> thanks for answering,
> Paul
I don't believe you. If that really is all it says then there is something seriously wrong.
> On 7 Nov, 14:46, profke <pwieg...@gmail.com> wrote:
> > On 7 nov, 15:40, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> > > On 7 Nov, 14:18, profke <pwieg...@gmail.com> wrote:
> > > > Hi everybody,
> > > > I have a very simple table, with one field of type double in it. I > > > > want to search for values in this column, so, I indexed it. But MySQL > > > > does not seem to use the index. If I query for a exact value, it does: > > > > ie: where col=5.3435 I get the one record which matches. But if I type > > > > col>5, I get the correct results (obviously!) but the index is no > > > > longer used. As i want to query for values, for instance, between 5 > > > > and 6, and I expect a lot of records on this table, and a lot of > > > > queries as well, this is worrisome... How can I get MySQL to use the > > > > index?
> > > > Interesting: if I sort the table on the column, it does not use the > > > > index either...
> > > > Anybody got any ideas?
> > > > thanks!
> > > > Paul
> > > What does an explain say?
> > Well, it simply says: possible_keys: null
> > thanks for answering,
> > Paul
> I don't believe you. If that really is all it says then there is > something seriously wrong.
On 7 nov, 15:49, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 7 Nov, 14:46, profke <pwieg...@gmail.com> wrote:
> > On 7 nov, 15:40, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> > > On 7 Nov, 14:18, profke <pwieg...@gmail.com> wrote:
> > > > Hi everybody,
> > > > I have a very simple table, with one field of type double in it. I > > > > want to search for values in this column, so, I indexed it. But MySQL > > > > does not seem to use the index. If I query for a exact value, it does: > > > > ie: where col=5.3435 I get the one record which matches. But if I type > > > > col>5, I get the correct results (obviously!) but the index is no > > > > longer used. As i want to query for values, for instance, between 5 > > > > and 6, and I expect a lot of records on this table, and a lot of > > > > queries as well, this is worrisome... How can I get MySQL to use the > > > > index?
> > > > Interesting: if I sort the table on the column, it does not use the > > > > index either...
> > > > Anybody got any ideas?
> > > > thanks!
> > > > Paul
> > > What does an explain say?
> > Well, it simply says: possible_keys: null
> > thanks for answering,
> > Paul
> I don't believe you. If that really is all it says then there is > something seriously wrong.
Well, I can give you the whole query, and explain too:
profke wrote: > On 7 nov, 15:49, Captain Paralytic <paul_laut...@yahoo.com> wrote: >> On 7 Nov, 14:46, profke <pwieg...@gmail.com> wrote:
>>> On 7 nov, 15:40, Captain Paralytic <paul_laut...@yahoo.com> wrote: >>>> On 7 Nov, 14:18, profke <pwieg...@gmail.com> wrote: >>>>> Hi everybody, >>>>> I have a very simple table, with one field of type double in it. I >>>>> want to search for values in this column, so, I indexed it. But MySQL >>>>> does not seem to use the index. If I query for a exact value, it does: >>>>> ie: where col=5.3435 I get the one record which matches. But if I type >>>>> col>5, I get the correct results (obviously!) but the index is no >>>>> longer used. As i want to query for values, for instance, between 5 >>>>> and 6, and I expect a lot of records on this table, and a lot of >>>>> queries as well, this is worrisome... How can I get MySQL to use the >>>>> index? >>>>> Interesting: if I sort the table on the column, it does not use the >>>>> index either... >>>>> Anybody got any ideas? >>>>> thanks! >>>>> Paul >>>> What does an explain say? >>> Well, it simply says: possible_keys: null >>> thanks for answering, >>> Paul >> I don't believe you. If that really is all it says then there is >> something seriously wrong.
> On 7 nov, 15:49, Captain Paralytic <paul_laut...@yahoo.com> wrote: >> On 7 Nov, 14:46, profke <pwieg...@gmail.com> wrote:
>>> On 7 nov, 15:40, Captain Paralytic <paul_laut...@yahoo.com> wrote: >>>> On 7 Nov, 14:18, profke <pwieg...@gmail.com> wrote: >>>>> Hi everybody, >>>>> I have a very simple table, with one field of type double in it. I >>>>> want to search for values in this column, so, I indexed it. But MySQL >>>>> does not seem to use the index. If I query for a exact value, it does: >>>>> ie: where col=5.3435 I get the one record which matches. But if I type >>>>> col>5, I get the correct results (obviously!) but the index is no >>>>> longer used. As i want to query for values, for instance, between 5 >>>>> and 6, and I expect a lot of records on this table, and a lot of >>>>> queries as well, this is worrisome... How can I get MySQL to use the >>>>> index? >>>>> Interesting: if I sort the table on the column, it does not use the >>>>> index either... >>>>> Anybody got any ideas? >>>>> thanks! >>>>> Paul >>>> What does an explain say? >>> Well, it simply says: possible_keys: null >>> thanks for answering, >>> Paul >> I don't believe you. If that really is all it says then there is >> something seriously wrong.
> Well, I can give you the whole query, and explain too:
> mysql> explain select * from item where lat>50; > +----+-------------+-------+------+-----------------+------+--------- > +------+------+-------------+ > | id | select_type | table | type | possible_keys | key | key_len | > ref | rows | Extra | > +----+-------------+-------+------+-----------------+------+--------- > +------+------+-------------+ > | 1 | SIMPLE | item | ALL | latlng,newindex | NULL | NULL | > NULL | 8 | Using where | > +----+-------------+-------+------+-----------------+------+--------- > +------+------+-------------+ > 1 row in set (0,00 sec)
> so..why doesnt it use the index?
> greetings,
> Paul
It is saying it is not using a key - not that possible keys is null, as you claimed in your previous statement. These are two entirely different things.
MySQL will not use an index if it thinks the retrieval will return a significant portion of the table. In a case like that, going to the index and retrieving individual rows based on the index can be slower than just doing a full table scan.
Now, you've only got 8 rows in your table, which is pretty small. A table scan would almost always be faster than access via an index.
-- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstuck...@attglobal.net ==================
> > > > > I have a very simple table, with one field of type double in it. I > > > > > want to search for values in this column, so, I indexed it. But MySQL > > > > > does not seem to use the index. If I query for a exact value, it does: > > > > > ie: where col=5.3435 I get the one record which matches. But if I type > > > > > col>5, I get the correct results (obviously!) but the index is no > > > > > longer used. As i want to query for values, for instance, between 5 > > > > > and 6, and I expect a lot of records on this table, and a lot of > > > > > queries as well, this is worrisome... How can I get MySQL to use the > > > > > index?
> > > > > Interesting: if I sort the table on the column, it does not use the > > > > > index either...
> > > > > Anybody got any ideas?
> > > > > thanks!
> > > > > Paul
> > > > What does an explain say?
> > > Well, it simply says: possible_keys: null
> > > thanks for answering,
> > > Paul
> > I don't believe you. If that really is all it says then there is > > something seriously wrong. > Well, I can give you the whole query, and explain too:
Well that is so kind of you.
I really don't get people like you. You come here to ask the experts and when we ask you for the necessary information, you refuse to give it, instead suggesting that you know better than us!
If you could read the explain output properly and knew how it all worked, you wouldn't have needed to ask here in the first place.
Jerry has given you the reason, but please in future, help us to help you by supplying the necessary information.
> > Well, I can give you the whole query, and explain too:
> Well that is so kind of you.
Sorry, but sarcasm does not really work, in cases like this....
> I really don't get people like you. You come here to ask the experts > and when we ask you for the necessary information, you refuse to give > it, instead suggesting that you know better than us!
I'm sorry, but where did I suggest I knew better than you? And I did not refuse to give any information. Of course, I should have given the FULL tabledef and expain in the first post, and by any next question I have, I will. I did not think it would matter, after me trying to explain the situation. Obviously, I was wrong.
> If you could read the explain output properly and knew how it all > worked, you wouldn't have needed to ask here in the first place.
> Jerry has given you the reason, but please in future, help us to help > you by supplying the necessary information.
I will. :-)
Thanks for helping me out.
I draw the conclusion that my query is correct, and my index too, but that MySQL won't use them because there is too little data in de table. That is slightly confusing, as this is only a test-dataset at the moment. However, testing apparently does not work in this way, and I need to have a large(ish) dataset for MySQL to tell me what it will be doing under live load.... Lesson learned...
On 8 Nov, 05:11, profke <pwieg...@gmail.com> wrote:
> Dear Captain,
> > > Well, I can give you the whole query, and explain too:
> > Well that is so kind of you.
> Sorry, but sarcasm does not really work, in cases like this....
> > I really don't get people like you. You come here to ask the experts > > and when we ask you for the necessary information, you refuse to give > > it, instead suggesting that you know better than us!
> I'm sorry, but where did I suggest I knew better than you? And I did > not refuse to give any information.
Well I asked what the explain said and instead of actually telling me what the explain said, you decided to (mis)interpret what it said and offer your view instead. Which was why I then pointed out that "If you could read the explain output properly and knew how it all worked, you wouldn't have needed to ask here in the first place."
> I draw the conclusion that my query is correct, and my index too, but > that MySQL won't use them because there is too little data in de > table. That is slightly confusing, as this is only a test-dataset at > the moment. However, testing apparently does not work in this way, and > I need to have a large(ish) dataset for MySQL to tell me what it will > be doing under live load.... Lesson learned...
MySQL like other RDBMS has an optimiser which tries to make the best query it can in the current circumstances. That is why there are statistics and optimise functions. For when it fails to come up with the best query, there is the ability to force the use of an index. This is all documented in the manual.
|
