MySQL Forums
Forum List  »  Performance

Re: 30M rows - is dynamic sorting and selecting even possible?
Posted by: Rick James
Date: April 15, 2009 10:33AM

Is dynamic possible? Sure. How long do you want to wait? Have you set the appropriate timeout settings on the web server, etc so they won't give up and return a "500: server error"?

Instead, I take the approach of speeding up the queries.

Re "left off"
* Pick a unique index -- pick the PK if none requested.
* ORDER BY that.
* First time, $leftoff = a minimum value (eg, 0 for an AUTO_INCREMENT)
* SELECT ... WHERE id > $leftoff ORDER BY id LIMIT 10000
* find last id of that set, provide that as the new value for $leftoff
* Give that back as part of the return value to the user, so he can use it for the next query.

Picking up "where you left off" avoids having to stop over the ones you already sent.

Having no ORDER BY can lead to unpredictable results -- If an insert/delete comes in, the counting may lead to gaps/dups between pages. ORDER BY and 'left off' completely eliminate this problem.

There are many variations on the above scheme, depending on the type of "id", etc. It gets messier (but not impossible) if the index is not unique, or it is a compound index.

Is this MyISAM or InnoDB? (Some details vary depending on engine.) Values of key_buffer_size and innodb_buffer_pool_size ?

Re insert performance...
"has an insert on that table about all three seconds" -- is that 20 INSERTs per minute? Even 20 per second is not a problem.
The more indexes, the more work the INSERT has to do. It has to insert the data somewhere (engine differences here), and update a block for each index. So, 20 indexes could mean 21 disk hits (depending on a lot of things). If the index blocks all fit in cache, no reads are needed, and writes can be delayed, making an INSERT feel fast (assuming 20/minute). If the indexes are too big to be cached, some number of reads will occur, and 20/second might be hard to attain.

Note: Get rid of the first of these; the second "covers" it.
INDEX (foo)
INDEX (foo, bar)
But this is distinctly different:
INDEZ (bar, foo)

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.