This is more an inner workings of mysql question. I have an innodb
table with 1+ billion rows. I took a small subset of that data and put
it into an idenitcal table structure in another DB. I wrote a query
that returned me 400K rows of data in 17 seconds from the new table.
The same query on the old table has been running for over an hour.

Is it the fact that the indexes on my new table are so much smaller
and the data is easier to retrieve? Or are my indexes on my old table
hosed? Or is the original table itself hosed? Being that the 1B row
table is the bread and butter of the operation and optimize table isnt
a likely fix here.

Any input is appreciated.

Jim

On 11/18/2010 10:00 AM, Jim wrote:
I very much doubt your table or indexes are corrupt - you would almost
definitely see other problems. Much more likely is the huge (2500x)
difference in table size.

What does EXPLAIN show you for each table? Look at your query - do you
have the appropriate indexes?

It's better to diagnose the problem than grasp at straws, trying to find
an answer.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Nov 18, 3:00*pm, Jim <jtbutle... (AT) comcast (DOT) net> wrote:
Who can tell based on what you have told us? When I was to be a patrol
for the UK Automobile Association, someone once said to me "My car
won't start, could it be the battery?" as in your case, the answer has
to be yes. Of course in his case, it could equally have been that he
had no fuel, or he hadn't put the key in and turned it, or any of many
other reasons.

So in your case, it "could" be any of the things that you mentioned,
but I very much doubt it is any of them. My crystal ball is in the
workshop at the moment, but if you'd care to supply some useful
details (a CREATE TABLE script and a copy of the long running query),
then we just might be able to offer you some useful advice.