how create index for substring of fields? (to say, from position 3 to 8 of field).

[mig]

Hello People, sorrry for this probably basic question, but I have
search "index" mysql command and not found answer.

Is there some way of redefine a database field by "breaking" it into
pieces and giving a "virtual field name" to every piece, in a way
normal indexs can use those pieces?

Here an exampe:

I have a database field "date" with values in the form mm/dd/yyyy ,
but I want to index it in the form yyyy/mm/dd , so is there some
command to create virtual field "year = date(7,4)" "month = date(4,2)"
and "day = date(1,2)" to allow index by year + month + day?

case this be impossible, what would be a good solution for this
problem without duplicating fields ?

thanks

[Jerry Stuckle]

No, but why are you using a VARCHAR column format? Why don't you use
the DATE column format?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================

[mig]

> jstuck...@attglobal.net
> ==================- Hide quoted text -
>
> - Show quoted text -

This is very old table with date stored in to make easy "order by"
commands by selecting mid(mydate,n,m) pieces of it. But now accesses
to this table has grown exponentially and "order by" custs too much
CPU for me since it uses temporary tables. This the reason I want to
redefine indexes avoiding modify field or table structure. I have also
other non-date fields with same problem of indexing parts of it. I can
´t believe zillions MySql users never needed this feature.

[The Natural Philosopher]

its certainly possible to select parts of a field using functions, but
I don't think you can index on less than a complete field.

As to how you can implement fast searches of partial fields using
indices I do not know. Axel may.

[Jerry Stuckle]

On 10/21/2011 7:30 PM, mig wrote:
> On Oct 21, 6:13 pm, Jerry Stuckle<jstuck...@attglobal.net> wrote:
>> On 10/21/2011 3:25 PM, mig wrote:
>>
>
>>
>> No, but why are you using a VARCHAR column format? Why don't you use
>> the DATE column format?
>>
>>

>> - Show quoted text -
>
> This is very old table with date stored in to make easy "order by"
> commands by selecting mid(mydate,n,m) pieces of it. But now accesses
> to this table has grown exponentially and "order by" custs too much
> CPU for me since it uses temporary tables. This the reason I want to
> redefine indexes avoiding modify field or table structure. I have also
> other non-date fields with same problem of indexing parts of it. I can
> ´t believe zillions MySql users never needed this feature.

DATE types have been around since the beginning of MySQL, IIRC.

And yes, zillions of MySQL users use this feature - the define the
column correctly (as a DATE type).

Fix your implementation and the problem will go away.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

jstu...@attglobal.net
==================

[Gordon Burditt]

> Is there some way of redefine a database field by "breaking" it into
> pieces and giving a "virtual field name" to every piece, in a way
> normal indexs can use those pieces?

You might possibly do this with a view. It is probably better to
break it into pieces before storing it in the database, or to store
a reasonable representation in the first place.

> I have a database field "date" with values in the form mm/dd/yyyy ,

Use the format MySQL wants for dates: a DATE or DATETIME column.
Use, say, str_to_date() for converting data on the way in and
date_format() on the way out. Indexing and date comparisons will
work the way you probably want them to, and probably more efficiently
than anything you could kludge up using views.

> but I want to index it in the form yyyy/mm/dd , so is there some
> command to create virtual field "year = date(7,4)" "month = date(4,2)"
> and "day = date(1,2)" to allow index by year + month + day?
>
> case this be impossible, what would be a good solution for this
> problem without duplicating fields ?

Only temporarily duplicate fields.

A suggested way to convert would be:

ALTER TABLE table add newfield date; (optionally with NOT NULL)
UPDATE table set newfield = str_to_date(oldfield, '%m/%d/%Y');
Fix your code to use newfield instead of oldfield, and test it.
That includes both the code that puts data into the database, and
code that uses it.
When you've got that done:
ALTER TABLE table drop oldfield;

[mig]

On Oct 21, 9:52 pm, gordonb.mw...@burditt.org (Gordon Burditt) wrote:

>
> A suggested way to convert would be:
>
> ALTER TABLE table add newfield date;       (optionally with NOT NULL)
> UPDATE table set newfield = str_to_date(oldfield, '%m/%d/%Y');
> Fix your code to use newfield instead of oldfield, and test it.
> That includes both the code that puts data into the database, and
> code that uses it.
> When you've got that done:
> ALTER TABLE table drop oldfield;

Ok guys , thanks, you WON ! , but these answer were not the ones I
wanted to hear. Either I will need duplicate data in the table (bad
practice) or I will need change lot of scripts to use new format.

Please notice this example is just the easiest I have to solve,
because I also have some old fields where now I need to index pieces
of them. Lets say cod = company+account+number but now I need index by
account+number+company . If MySql had a single "virtual redefine"
field these tasks would be immediately addressed.

This the reason I asked "how it is possible nobody into zillions MySql
users never needed this feature?"

[Axel Schwenke]

mig <meu...@gmail.com> wrote:

> ... these answer were not the ones I wanted to hear.

If you don't like answers, then don't ask!

> Either I will need duplicate data in the table (bad
> practice) or I will need change lot of scripts to use new format.

Yes. Life sucks.

> Please notice this example is just the easiest I have to solve,
> because I also have some old fields where now I need to index pieces
> of them. Lets say cod = company+account+number but now I need index by
> account+number+company.

Why do you "need" this index? What is wrong with having this *not*
indexed?

> If MySql had a single "virtual redefine"
> field these tasks would be immediately addressed.

MariaDB does have virtual columns. And the materialized flavor even
supports indexes. Of course this is data duplication, too.

> This the reason I asked "how it is possible nobody into zillions MySql
> users never needed this feature?"

MySQL never had indexes for computed fields. And probably will never
have them. If you really need such a thing, then you have to bite the
bullet and duplicate the data.

You haven't given much detail. But as of now it looks like your
database is poorly designed. And now you try to "fix" this by asking
for exotic features.


XL

[Jerry Stuckle]

Maybe because people defined the data correctly in the first place?

[mig]

On Oct 24, 9:38 am, Axel Schwenke <axel.schwe...@gmx.de> wrote:

>
> You haven't given much detail. But as of now it looks like your
> database is poorly designed. And now you try to "fix" this by asking
> for exotic features.
>
> XL

I said, this is an old database, six years ago nobody could imagine
these codes could have some meaning into, they just gave them us to
put into DB. Now people discovered the hidden subdivisions and want to
sort according to. About the question "why I need this index?", as I
explained, table had exponential grown accesses, so making "order by"
sucks all my CPU with tmp tables, I really need an index.

Now I am already working (a lot) to make these changes. Cutting
fields, re-loading (BIG) databases, re-programming lot of scripts,
testing them and justifying my salary.

I remember the old fashioned days when programming in (PC DOS) 8 bits
dataflex, and yes, 8 bits dataflex had "redefine fields" which you
could index in a flash. I remember working with COBOL, and yes, COBOL
had "redefine fields" also. This is not an "exotic" feature, but a
feature MySql programmers forgot to develop.

[Jerry Stuckle]

Neither dataflex nor cobol are databases. They are languages.

What database has such a feature?

I agree with Axel - you are asking for exotic features to cover up a bad
design. Design the database correctly and you don't have these problems.

[Kees Nuyt]

Ok, so you have to refactor your database.

Create newly redesigned tables with a new name, make sure
all contraints are in place. The new tables don't have to
be 1 to 1 with the original ones, this is a great
opportunity to improve the schema.

Carefully fill them with the properly standardized,
normalized data from the old tables. Solve all constraint
conflicts.

Move the old tables out of the way (drop or rename them).
Create views using the name of every old table you had to
refactor, in such a way they offer the same data with the
same column names to the application as the old tables
did.

Create instead of insert triggers on the views (dunno
whether MySQL supports those, perhaps you'll have to
choose some other rdbms) that transforms the NEW.data
into inserts into the refactored tables. Do the same for
updates and deletes.

Script all steps above from beginning to end, so this
refactoring can be easily repeated several times (you
won't get it right the first time) and test them
thoroughly in another environment before touching your
production database.

Make sure to have a fallback plan for the production
conversion.
Best regards,
--
( Kees Nuyt
)
c[_]

[mig]

On Oct 26, 10:51 am, Kees Nuyt <k.n...@nospam.demon.nl> wrote:
> On Wed, 26 Oct 2011 00:56:49 -0700 (PDT), mig
>

> c[_]- Hide quoted text -

>
> - Show quoted text -

Ok, thanks for your helping, but now I see my problem is a bit more
complicate than single field translations. In mysql "order by"
statement it is very easy to say "field a descending, field b
ascending" and it works fine. You can have a mix of ascending /
descending fields on the fly.

BUT now I am learning mysql "create index" command has not
"descending" order (you can write DESC statement but just for use in
future releases, actually it doesn´t work).

Then what´s the solution here?. If they were numeric fields I could
duplicate fields and store "max value minus current field value" or
something else into to obligue index to order by descending any way.

But what when my fields are ascii fields?, is there a way of create
indexes which mix ascending and descending values?

It appears a "dead end" route for me unless I create a kind of crypto
code "a = z" and so to force fields to order descending.

[Hans Castorp]

Jerry Stuckle wrote on 26.10.2011 13:25:
> What database has such a feature?

If you are referring to indexing an expression: most of the DBMS out there allow that.

[Gordon Burditt]

> Ok, thanks for your helping, but now I see my problem is a bit more
> complicate than single field translations. In mysql "order by"
> statement it is very easy to say "field a descending, field b
> ascending" and it works fine. You can have a mix of ascending /
> descending fields on the fly.
>
> BUT now I am learning mysql "create index" command has not
> "descending" order (you can write DESC statement but just for use in
> future releases, actually it doesn´t work).

You seem to be assuming that MySQL cannot traverse an index backwards.
Do you have evidence for this assumption?

[Axel Schwenke]

mig <meu...@gmail.com> wrote:
>
> ... now I see my problem is a bit more

> complicate than single field translations. In mysql "order by"
> statement it is very easy to say "field a descending, field b
> ascending" and it works fine. You can have a mix of ascending /
> descending fields on the fly.
>
> BUT now I am learning mysql "create index" command has not
> "descending" order (you can write DESC statement but just for use in
> future releases, actually it doesn't work).

Yes. So what?

> Then what's the solution here?. If they were numeric fields I could
> duplicate fields and store "max value minus current field value" or
> something else into to obligue index to order by descending any way.

I wonder what you try to achieve.
Or what you think indexes are good for.

Just create the indexes on the fields that need them. And maybe
start with no indexes at all; except PK and UNIQUE constraints.
It depends on your queries if you need additional indexes. And
remember: every additional index slows down DML. And eats disk
and memory.


XL

[mig]

Maybe I expressed bad, I have two ascii fields in the same table , I
want this indexed result

field A field B
==== =====

zzzz aaaa
zzzz bbbb
zzzz cccc
yyyy aaaa
yyyy bbbb
yyyy cccc
xxxx aaaa
xxxx bbbb
xxxx cccc

"order by" works fine, but I need replace it because performance
problems (lot of tmp tables), I need to build a true index to perform
this task.

According mysql 5.5 doc

"An index_col_name specification can end with ASC or DESC. These
keywords are permitted for future extensions for specifying ascending
or descending index value storage. Currently, they are parsed but
ignored; index values are always stored in ascending order"

[Jacek Krysztofik]

On 26.10.2011 22:14, mig wrote:
> index values are always stored in ascending order

Latin scripts are read left to right, Hebrew and Arabic the other way
around and far-eastern pictographs top-down. I don't see a problem.

[Axel Schwenke]

mig <meu...@gmail.com> wrote:

> On Oct 26, 5:28=A0pm, Axel Schwenke <axel.schwe...@gmx.de> wrote:
>>
>> I wonder what you try to achieve.
>> Or what you think indexes are good for.

> I have two ascii fields in the same table , I
> want this indexed result
>
> field A field B
> ==== ====
>

> zzzz aaaa
> zzzz bbbb
> zzzz cccc
> yyyy aaaa
> yyyy bbbb
> yyyy cccc
> xxxx aaaa
> xxxx bbbb
> xxxx cccc
>
> "order by" works fine, but I need replace it because performance
> problems (lot of tmp tables), I need to build a true index to perform
> this task.

It seems the misunderstanding is here. There are many reasons
why a query could be slow. Sorting the result is not a typical
reason. Accessing rows in index order (to save the external sort)
is not necessarily better than a table scan followed by sorting.
The reason is random I/O vs. streaming I/O.

What you are doing here, is called "premature optimization".
A good indexing strategy is what I said in my last post:

1. start with a normalized schema; the only indexes should be
those to enforce constraints
- primary keys
- unique constraints
- foreign key constraints

2. look at your queries and how long they take to execute.
The slow query log could be helpful here.

3. if there are no (too) slow queries or if the slow queries
are running only occasionally, then stop.

Probably there will be slow queries. You can now start to optimize
the schema (and/or the queries). Start with the query that runs most
often. Then do:

A. run the query through EXPLAIN and understand(!) the result

B. add indexes to the tables accessed by the query and/or
rewrite the query such that it can (better) use indexes

C. verify that the problem query is really faster now

Probably this will have effect on other queries too. So go back
to 2. and repeat.

In step B. there are many additional possibilities. To name a few:

- add index hints to queries
- rewrite subqueries as joins
- add redundant data (denormalize the schema)
- replace views or subqueries by properly indexed temporary tables


HTH, XL