 | |
how create index for substring of fields? (to say, from position 3 to8 of field).
comp.databases.mysql comp.databases.mysql
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: how create index for substring of fields? (to say, from position3 to 8 of field). -
10-26-2011
, 06:25 AM
|
On Oct 24, 9:38 am, Axel Schwenke<axel.schwe... (AT) gmx (DOT) 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. |
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.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================
#12
| |||
| |||
|
|
On Oct 24, 9:38*am, Axel Schwenke <axel.schwe... (AT) gmx (DOT) 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. |
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[_]
#13
| |||
| |||
|
|
On Wed, 26 Oct 2011 00:56:49 -0700 (PDT), mig 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[_]- Hide quoted text - - Show quoted text - |
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.
#14
| |||
| |||
|
|
What database has such a feature? |
#15
| |||
| |||
|
|
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?
#16
| |||
| |||
|
|
... 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). |
Quote:
|
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. |
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
#17
| |||
| |||
|
|
mig <meu... (AT) gmail (DOT) 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 |
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"
#18
| |||
| |||
|
|
index values are always stored in ascending order Latin scripts are read left to right, Hebrew and Arabic the other way |
#19
| |||
| |||
|
|
On Oct 26, 5:28=A0pm, Axel Schwenke <axel.schwe... (AT) gmx (DOT) 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. |
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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.