Mysql performance on 6 million row table

by

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

More Related Contents:

  1. DOs and DONTs for Indexes [closed]
  2. MySQL performance: multiple tables vs. index on single table and partitions
  3. Higher cardinality column first in an index when involving a range?
  4. Does Order of Fields of Multi-Column Index in MySQL Matter
  5. MySQL load data infile – acceleration?
  6. Would it help to add index to BIGINT column in MySQL?
  7. How can i optimize MySQL’s ORDER BY RAND() function?
  8. How do I add indexes to MySQL tables?
  9. Is the primary key automatically indexed in MySQL?
  10. MySQL: Fastest way to count number of rows
  11. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  12. MySQL vs MongoDB 1000 reads
  13. How to see indexes for a database or table in MySQL?
  14. Speeding up row counting in MySQL
  15. UUID performance in MySQL?
  16. MySQL “IN” operator performance on (large?) number of values
  17. MySQL varchar index length
  18. Optimal MySQL settings for queries that deliver large amounts of data?
  19. What is better in MYSQL count(*) or count(1)?
  20. Mysql Improve Search Performance with wildcards (%%)
  21. How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?
  22. MySQL view performance
  23. How to disable index in innodb
  24. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  25. MySQL 5.0 indexes – Unique vs Non Unique
  26. sql group by versus distinct
  27. Two single-column indexes vs one two-column index in MySQL?
  28. MySQL – why not index every field?
  29. ALTER TABLE ADD COLUMN takes a long time
  30. MySQL: how to index an “OR” clause

Leave a Comment