Speeding up MySQL queries using an index

If you’re doing a query on a very large db then always make sure you’ve got an index on fields that are being used to group (WHERE) data. In my case, I was doing a query on a table with 249M entries that was using a WHERE on a ‘symbol’ field. By adding an index to the symbol field, the data is grouped by MySQL and is returned way, way faster (hundreds of times faster)

DO this at your own risk and DO NOT do this on a DB unless you have a full backup.

ALTER TABLE data.minute ADD INDEX(symbol);

data.minute is the table

symbol is the field you’re adding the index to.

More info and detail can be found here

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s