metamorph index

[jlin]

Hi,
I kept getting this error message. Here's the background:

Table: items
id int
Name varchar
Description varchar

Index:
create metamorph inverted index xitems1 on items(Name\Description);

Here's what's in my code:
<SQL "select Name from items where Name\Description likep 'shoes' order by Name">
<SQL>

The sql statement returns the result sorted alphabetically, but it always give me the following error messages:

<!-- 115 /inny/innydl:122: Field Name non-existent -->
<!-- 200 /inny/innydl:122: Can't handle ORDER BY with compound index -->

Did I do anything wrong?

Thanks!

[John]

No, you didn't do anything wrong. Are you sure that is the index you have? Those are not error messages, but informational messages, indicating that the ORDER BY was not able to use the compound index.

[jlin]

Hi John,

I actually have one field called List_ID in the items table.

So the exact index I have is:

create metamorph inverted index xitems1 on items(Name\Description,List_ID);

When I do "select Name from items where Name\Description likep 'shoes' and List_ID=2 order by Name", everything seems to be fine, those informational messages are not showing up.

Thanks for the fast response!

[bart]

You should probably not be using likep with an "order by" (unless you really know why you're doing it). Using "like" instead of "likep" would be more correct and save CPU time in most cases.

[jlin]

That does make sense, thanks!