Mysql performance on 6 million row table

问题:

One day I suspect I'll have to learn hadoop and transfer all this data to a non-structured database, but I'm surprised to find the performance degrade so significantly in such a short period of time.

I have a mysql table with just under 6 million rows. I am doing a very simple query on this table, and believe I have all the correct indexes in place.

the query is

SELECT date, time FROM events WHERE venid='47975' AND date>='2009-07-11' ORDER BY date

the explain returns

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  updateshows     range   date_idx    date_idx    7   NULL    648997  Using where

so i am using the correct index as far as I can tell, but this query is taking 11 seconds to run.

The database is MyISAM, and phpMyAdmin says the table is 1.0GiB.

Any ideas here?

Edited: The date_idx is indexes both the date and venid columns. Should those be two seperate indexes?

回答1:

What you want to make sure is that the query will use ONLY the index, so make sure that the index covers all the fields you are selecting. Also, since it is a range query involved, You need to have the venid first in the index, since it is queried as a constant. I would therefore create and index like so:

ALTER TABLE events ADD INDEX indexNameHere (venid, date, time);

With this index, all the information that is needed to complete the query is in the index. This means that, hopefully, the storage engine is able to fetch the information without actually seeking inside the table itself. However, MyISAM might not be able to do this, since it doesn't store the data in the leaves of the indexes, so you might not get the speed increase you desire. If that's the case, try to create a copy of the table, and use the InnoDB engine on the copy. Repeat the same steps there and see if you get a significant speed increase. InnoDB does store the field values in the index leaves, and allow covering indexes.

Now, hopefully you'll see the following when you explain the query:

mysql> EXPLAIN SELECT date, time FROM events WHERE venid='47975' AND date>='2009-07-11' ORDER BY date;

id  select_type table  type  possible_keys        key       [..]  Extra
1   SIMPLE   events range date_idx, indexNameHere indexNameHere   Using index, Using where

回答2:

Try adding a key that spans venid and date (or the other way around, or both...)

回答3:

I would imagine that a 6M row table should be able to be optimised with quite normal techniques.

I assume that you have a dedicated database server, and it has a sensible amount of ram (say 8G minimum).

You will want to ensure you've tuned mysql to use your ram efficiently. If you're running a 32-bit OS, don't. If you are using MyISAM, tune your key buffer to use a signficiant proportion, but not too much, of your ram.

In any case you want to run repeated performance testing on production-grade hardware.

回答4:

Try putting an index on the venid column.