quieries taking a while - key problem?

[crabpot8]

I am decent at queries, but recently i made a new DB with about 50000+ entries. Queries are taking almost 20+ seconds, and i think it is that i dont have decently structured keys - this is the first time I was the one that created the DB structure. Here is the info that seemed relevant - ANY HELP APPRECIATED

There is one 'primary' key - a mix of server, timestamp, and ipaddress
This is a few server access logs put into a database

Also - if you know of a good tutorial on keys - i have been trying to read up - but everywhere either deals with what they are or how to optimize queries, and none of them seem to deal with the issue together

crabpot8

_______________ _______________ ______________

Field Type Null Default
ip_address char(15) No
timestamp timestamp No CURRENT_TIMESTA MP
filename varchar(1000) Yes NULL
http_status smallint(6) Yes NULL
bytes_sent int(11) Yes NULL
client_info text Yes NULL
client_id text Yes NULL
client_stat_res ults text Yes NULL
file_size int(11) Yes NULL
send_time int(11) Yes NULL
resends smallint(6) Yes NULL
failed_resends smallint(6) Yes NULL
pres_id smallint(6) Yes NULL
file_time int(11) Yes NULL
helix_ enum('a', 'b') No a


Indexes:
Keyname Type Cardinality Field
PRIMARY PRIMARY 55485 ip_address
timestamp
helix_


Space usage:
Type Usage
Data 39,552 KiB
Index 0 B
Total 39,552 KiB
Row Statistics:
Statements Value
Format Compact
Rows 73,709
Row length ø 549
Row size ø 549 B
Creation Feb 04, 2008 at 12:36 PM
_______________ _______________ _______________ ___

here are the types of query i have to run ran - each took over 20 seconds

SELECT COUNT(*) FROM rmaccess WHERE timestamp BETWEEN 20080225004627 AND 20080225014627
 
SELECT COUNT(*) FROM rmaccess WHERE timestamp BETWEEN 20080225014627 AND 20080225024627
 
SELECT COUNT(*) FROM rmaccess WHERE timestamp BETWEEN 20080225024627 AND 20080225034627

Please enclose any code within the proper code tags. See the Posting Guidelines on how to do that.

MODERATOR

[mwasif]

Hi crabpot8,

Your query is not using the INDEX at all. Use EXPLAIN to confirm this.

Reason: In multiple column indexes, they are always read from left to right. You have defined the index in the order

ip_address
timestamp
helix_

To properly use the index for your said query, you must have to create a combine index in the following order

timestamp
ip_address
helix_

Let us know if you need more help.

[crabpot8]

Hi crabpot8,

Your query is not using the INDEX at all. Use EXPLAIN to confirm this.

Reason: In multiple column indexes, they are always read from left to right. You have defined the index in the order

ip_address
timestamp
helix_

To properly use the index for your said query, you must have to create a combine index in the following order

timestamp
ip_address
helix_

Let us know if you need more help.

Thanks a lot! I actually did it a little differently - I am not sure if its maximum effectiveness but it definitely works better. I needed the three columns to be unique together, but I am generating charts of server stats so i normally use the timestamps in my queries. So i did this - any opinions or am i headed in the right direction?

These are in the correct order that i have them in my table. My queries work in a reasonable time now, but if I made the second key timestamp, ip_address, helix_ , would it help even more?


I still would love a good tutorial on how to make indexes on data like this that does not need a unique ID, but has a column or set of columns that will suffice.

Thanks for the help!
~crabpot

[crabpot8]

Hm - I tried changing my keys up and here are the results -

SQL Statement -

SELECT COUNT( timestamp )
FROM rmaccess
WHERE timestamp
BETWEEN 20080225034627
AND 20080225044627

SQL Statement (with different timestamp values) Ran about 150 times on both



Keys version 1

• Primary - timestamp,helix _
• Unique - ip_address, timestamp, helix_

Explain Of SQL

id - 1
select_type - SIMPLE
table - rmaccess
type - range
possible_keys - PRIMARY
key - PRIMARY
key_len - 4
ref - NULL
rows - 6
Extra - Using where; Using index
 

Speed Test Results

1st - 0.97661185264587
2nd - 0.71671605110168 
3rd - 0.67619180679321 

Keys version 2

• Primary - timestamp,helix _
• Unique - timestamp, ip_address, helix_

Explain of SQL

id - 1
select_type - SIMPLE
table - rmaccess
type - range
possible_keys - PRIMARY, unique
key - PRIMARY
key_len - 4
ref - NULL
rows - 6
Extra - Using where; Using index
 

Speed Test Results

1st - 1.0137181282043 
2nd - 0.66393899917603 
3rd - 0.67385196685791 

Interesting Note: phpmyadmin said this with version two:
'PRIMARY and INDEX keys should not both be set for column `timestamp`'



So from looking at the explains - I am guessing (i did a little reading, but have not had time to do a lot) That the two queries are equivalent with the different indexes. Strangely enough - perhaps this is what phpmyadmin meant by should not be using for two queries - the version one, with timestamp in the middle of the unique rather than the front - ran faster in subsequent tests (as in perhaps it cached more). The only reason i can think of is that if it used two keys in the query it might try to cache them both, versus if it just used one it might cache more and return other results faster.

~crabpot8

PS - i know this is a lot to dig results from, but I really cant find a good tutorial on this