Reordering the primary key wouldn't necessarily speed up the key check.
The reason for selecting a particular sequence within a primary key is to
put the columns that are available the most often - upfront - so that the
index will have at least something to bite on.
Can you parittion further? You want to shoot for reading no more than a 1%
slice for a given query. If possible.
Can you partition by ranges of a column? Values 000001 to 1000000 in
partition 1, 100001 to 2000000 in partition 2, etc? Anything that's
specified consistently in every query?
Then again -
if your table is huge...
and your queries are all over the place...
and the user can specify any search criteria...
and there's no predictable pattern you can partition on...
and you frequently add and/or remove lots of data from your table...
and your queries pull lots of rows...
You might be out of luck with MySQL ... sorry.
You may need to switch to a database that has a parallel query facility.
Then - every query becomes a massive table scan but gets divided into
multiple concurrent subqueries - and overall the job finishes in a
reasonable amount of time. The epitomy of brute force. It's hard to
rationalize initially but after a while you see it's the only way to go.
Remember - indexes are no longer required.
We have a billion row 100GB table the users search any and every way.
Response time is less than a minute.
We are anxiously waiting to see this technology added to MySQL. Maybe one
day we'll have some money to contribute to the effort. Parallel query is
not trivial. That's why these databases are expensive.
I can send you more details privately if you are interested.
matt ryan <[EMAIL PROTECTED]>
07/14/2004 12:27 PM
To: [EMAIL PROTECTED]
cc:
Subject: Re: Mysql growing pains, 4 days to create index on one table!
[EMAIL PROTECTED] wrote:
>You may want more indexes but you might be getting killed because you
already have too many.
>
>To test - try loading into a table without indexes and see if it makes a
difference.
>
>At the very least - check to see if the primary index which starts with
'dic' can make your special 'dic' index superfluous.
>
>If write speed is a bottleneck you might consider Raid-1 instead of
Raid-5.
>
>Reading lots of rows via index is a killer. Depending on your hardware it
may be cheaper to table scan 50 rows than to read 1 via index. However,
this requires partitioning of the data based on some column which appears
in every query and acts as an initial filter. If you are lucky enough to
be in that situation - consider a MERGE table.
>
>
>
>
These tables are merged, the total table size is huge, on this
particular table, it's , 45,449,534 rows, however, all the merge tables
combined are 258,840,305 records
perhaps I should reorder the pimary key, putting the longest most unique
record up front, and the least unique at the end, would that speed up
the key check? I can tell that almost everything is read IO, very
little write IO
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
