Efficiently querying MySQL table with 15 million records

I have a question about optimization.

Basically I have 15 million records to search (going up by around 80K a day), and I wanted to be able to query this table as quicky an efficiently as possible.

I've added indexes on serverID and date, date and serverID respectivley. In some cases the serverDate index works well, producing results in a few seconds, which I can live with for now. However, some queries which would appear to be simpler (over shorter date ranges for example) show (using EXPLAIN) that more records must be searched...

Let me explain my table structure a little more:

table1(id, amount, cost, server, faction, url, seller, date, archived)
table2(id, servername, area, game, archived)

Lets say I want to search table1 (with 15 million records), when I know the server ID and the date range to search for. How to best go about doing this, or structuring the data to do this?

Am I doing something obviously wrong in indexing this table. Can you suggest any way to more efficiently query this table?

It might help to see how this query is being applied: http://www.eyeonmogs.com/beta/marketwatch/marketwatch.php

Please note: this is an unreleased tool and I'd like to keep it that way for the time being.

Cheers,

Mike

 

What does your SQL call look like?
I would just make sure to have the Primary Key on the id field, id field is an integer type, and no nulls are allowed on the id field. Also throw an index on the date field, make sure it's non-null, and always has valid date data.

Next run some tests. Do a select just depending on the ID. For instance select id, amount, cost, server, faction, url, seller, date, archived from table1 where (id > 1000000) AND (id < 10000000). - this should give you 9 million records - find out how long it takes.

Then do a similar query that would display 9 million records based on date like Select ........ from table2 where (date > 'ADateValue') AND (date < 'ALaterDateValue'). See where the bottle neck is.

Just do as many tests as you can - based on different fields in the where clause - then add/remove indexes and do more tests. In MsSQL Server their is an index tuning wizard (which takes care of this for you) but i'm not sure about MySQL. Good luck

 

Don't forget to take a look at the MySQL Runtime Information to check if there's a high number for Created_tmp_disk_tables, Key_reads, Opened_tables etc that are slowing it down and then make some tweaks if you've got access to the config file. Maybe I'm preaching to the converted though

 

Preach on brotha - preach on!

 

MySQL only uses one index per query. Try creating 2 combined indexes, one on Server ID and date, and one on date and Server id. Make sure to run Optimize table after the creation of these, so that the query optimizer has up-to-date distribution information on these indexes. Now run your queries; most likely, one index will always be favored for a particular access pattern. Delete the one this is not or not often used - presto.

 

Also, I have noticed that speeds change in queries depending on how the queries/joins are setup

For instance, if you do not use a WHERE clause and rather limit the original table call with a JOIN, it runs slow (I THINK). IE

Way 1:
SELECT a,b,c FROM tbl1
LEFT JOIN tbl2 ON asdf
WHERE tbl1.a = 'frank';

Way2 (may be slower, not sure. I heard the "new" way of querying is w/ out wheres in JOINED queries)
SELECT a,b,c FROM tbl1
LEFT JOIN tbl2 ON asdf AND tbl1.a = 'frank'


I know the syntax is wrong, but you get the idea

 

also try swapping ur where clauses around, sounds daft but can make a massive difference.

ie

Select * from whereever
Where ID = 3 AND date > '09/04/2006'

instead of

Select * from whereever
Where date > '09/04/2006' and ID = 3

Searches on integers are far far quicker than on dates / text fields!

Also try using DBCC checktable (tbl_TableName,repair_rebuild) on ur table, this may tidy it up a bit if u do a lot of deleting / amending of the data

 

Almost never do Select * - what happens when your tables grow and grow - and your selecting everything and your queries get slower and slower.

The only time I can think of to use select * is if you are encapsulating another query or when you don't know what you need to pull from the database.

- Just my 2 centZ

 

There are two non-query related things which helps bringing queries much faster, first is the SCSI disk (can make queries upto 40% faster) and second is, having mysql server and database on two differnet disks.

 

"select *" was used just as an example!!!

 

Use the EXPLAIN function to see what your query will be doing and how many rows it will need to touch to get it's results. You can refine the query from there without actually executing the query itself and see where you need to add indexes.

(ie: EXPLAIN SELECT * FROM tablename