Hi.
On Mon 2002-07-08 at 17:49:03 -0700, [EMAIL PROTECTED] wrote:
> Hello,
>
> I have _a lot_ of http log data to throw into a mysql db (currently over 1.5
> billion rows). New data is coming in all the time, so I don't want to lock
> myself into one set of big tables that are over 100 gigs each. I'd rather
> arrange this data into smaller chunks, then merge the tables together so it
> looks like one big table when my users perform sql queries. My biggest
> concern is speed right now, and the most common search on these tables will
> be 'count' queries. Currently 'count' queries take over a minute to
> perform. I'd love to get that number down. Here are some questions:
>
> 1) I've need multiple indexes in my tables (for instance, one table has 24
> fields, 18 of which should have keys). Should I index each table
> separately, or do I index the big merged "virtual" table?
You _have_ to index each table seperately and also to declare the
index on the MERGE table. The MERGE table is only a definition. It
will not create real index. But you need to define them to tell the
query optimizer that there are indeed indexes on the underlying
tables.
> 2) When creating the merged table, do I define keys, or do I not bother,
> since the individual tables are already indexed? Common sense says I should
> be indexing once over ALL tables, since the searches people perform are
> always going to span multiple tables.
See above.
> Any experiences you can throw out at me regarding 'merge' would be greatly
> appreciated.
MERGE tables are not optimized as well, because for some more complex
tasks, MySQL avoid to look up the tables as it does for MyISAM ones
(the point is a "quick" lookup in potentially hundreds of underlying
tables is not quick anymore).
Optimize indexes using EXPLAIN only on one of the seperate tables.
Then, after updating all tables, including the MERGE one, use USE
INDEX, STRAIGHT_JOIN and so on in your queries, whenever the MySQL
optimizer doesn't choose the optimal way you found while looking at
the single table.
Greetings,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
