Help with large tables and slow queries

m_tt

Ok here is the problem...

I have a hit tracking table, after it reaches roughly 2 million rows, the queries start to slow. A simple limit query such as this will take about 15 seconds to run.

SELECT * FROM track LIMIT 2962241 , 30

What is the most likely cause of this? I was thinking hardware limitations but we are already using a dedicated mysql server.

Does anyone have any ideas what it could be?

Here is the basic track table structure:

CREATE TABLE track (
track_id int(11) NOT NULL auto_increment,
add_time datetime default NULL,
ip_address varchar(16) NOT NULL default '000.000.000.000',
referrer int(11) default NULL,
ua int(11) default NULL,
host int(11) default NULL,
request_uri int(11) default NULL,
demo_id varchar(19) default NULL,
client_id mediumint(9) NOT NULL default '0',
site_id mediumint(9) NOT NULL default '0',
PRIMARY KEY (track_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Referrer, UA, Host, and Request_uri are all ID's only that link to their corresponding tables. It is not full user data.

bpat1434

Well, for 1, you can speed them up by only selecting the columns you need to show. Selecting * will decrease speed.

Yegg

Try don't use NULL values in fields, the usein of NULLS decrease the speed of MySQL Tables.
if your VARCHAR demo_id is always 19 chars in length or all ids above the same, then change ip_address and demo_id to CHAR TYPE, mysql works more faster with STATIC char fields.
if you don't delete track records, so use better so kind of queries: SELECT * FROM track WHERE track_id >= 2962241 LIMIT 30;
4 U need to chek up your server enviroment: pool/cache sizes + how fast is your hdd, does it have DMA enabled e.t.c.

DoctorWho

I have numerous tables with over 5 Million records.

10,000 RPM HDD, and 1 Meg of Ram and some Server Variables does the trick.

Index the Fields that you do the most searching on of course.

Yegg

you need to make selections in so way:

SELECT * FROM track WHERE track_id >= 2962241 and track_id< 2962271;

it will be much faster than I wrote before - with LIMIT 30

Also ofcourse server variables play much role, for you in this situation is more important key_buffer var - it must be enough big - to load table index file into memory

also before you run any query - try to write before it explain, e.g. "explain SELECT * FROM track WHERE track_id >= 2962241 and track_id< 2962271"; you will see how much records mysql will parse before will return answers to you, less lines he must to parse - then faster you will get your records, also you will see there if he will use indexes durign this query. maybe you need to make some indexes if you do some special select by value of other columns;