do i need an index for this?
Hi, I want to find the earliest and latest times in the time column of my table internet_usage: +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | uid | int(10) unsigned | | MUL | 0 | | | time | timestamp(14)| YES | | NULL| | | ip | int(10) unsigned | | | 0 | | | urlid| int(10) unsigned | | | 0 | | | timetaken| smallint(5) unsigned | YES | | 0 | | | cs_size | int(10) unsigned | YES | | 0 | | | sc_size | int(10) unsigned | YES | | 0 | | | method_ID| tinyint(3) unsigned | | | 0 | | | action_ID| tinyint(3) unsigned | | | 0 | | | virus_ID | tinyint(3) unsigned | | | 0 | | | useragent_ID | smallint(5) unsigned | | | 0 | | +--+--+--+-+-+---+ So far there are 324936160 rows. If I do : SELECT MIN(time) as earliest, MAX(time) as latest from internet_usage; I can see the query is going to run for a long time. Do I have to create an index on time to speed this up or is there another way of doing it? Thanks for any help. JS. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do i need an index for this?
In the last episode (Jul 14), J S said: I want to find the earliest and latest times in the time column of my table internet_usage: +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | time | timestamp(14)| YES | | NULL| | +--+--+--+-+-+---+ So far there are 324936160 rows. If I do : SELECT MIN(time) as earliest, MAX(time) as latest from internet_usage; I can see the query is going to run for a long time. Do I have to create an index on time to speed this up or is there another way of doing it? Most definitely. An index will make that query almost instantaneous. Mysql won't even have to hit the table at all, and just has to look at the first and last index blocks. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do i need an index for this?
Creating a key will make that query execute very fast, but if that is the only reason for the key you are going to be trading quite a lot of space for the speed of one query. How often are you going to run this query? If you have 324 million rows, then that index is going to consume somewhere in the order of 2G or more of disk space. Is it worth using all that space to make one query faster? --- J S [EMAIL PROTECTED] wrote: Hi, I want to find the earliest and latest times in the time column of my table internet_usage: +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | uid | int(10) unsigned | | MUL | 0 | | | time | timestamp(14)| YES | | NULL| | | ip | int(10) unsigned | | | 0 | | | urlid| int(10) unsigned | | | 0 | | | timetaken| smallint(5) unsigned | YES | | 0 | | | cs_size | int(10) unsigned | YES | | 0 | | | sc_size | int(10) unsigned | YES | | 0 | | | method_ID| tinyint(3) unsigned | | | 0 | | | action_ID| tinyint(3) unsigned | | | 0 | | | virus_ID | tinyint(3) unsigned | | | 0 | | | useragent_ID | smallint(5) unsigned | | | 0 | | +--+--+--+-+-+---+ So far there are 324936160 rows. If I do : SELECT MIN(time) as earliest, MAX(time) as latest from internet_usage; I can see the query is going to run for a long time. Do I have to create an index on time to speed this up or is there another way of doing it? Thanks for any help. JS. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do i need an index for this?
Thanks for your reply. Can I just check my syntax is right for creating the key? CREATE INDEX urlindex ON internet_usage (time); I ran this before but got an error 27 (eventhough the filesize ulimit was set to unlimited). So just wondered if I was using the wrong syntax? JS. In the last episode (Jul 14), J S said: I want to find the earliest and latest times in the time column of my table internet_usage: +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | time | timestamp(14)| YES | | NULL| | +--+--+--+-+-+---+ So far there are 324936160 rows. If I do : SELECT MIN(time) as earliest, MAX(time) as latest from internet_usage; I can see the query is going to run for a long time. Do I have to create an index on time to speed this up or is there another way of doing it? Most definitely. An index will make that query almost instantaneous. Mysql won't even have to hit the table at all, and just has to look at the first and last index blocks. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do i need an index for this?
In the last episode (Jul 14), J S said: Thanks for your reply. Can I just check my syntax is right for creating the key? CREATE INDEX urlindex ON internet_usage (time); I ran this before but got an error 27 (eventhough the filesize ulimit was set to unlimited). So just wondered if I was using the wrong syntax? Getting errno 27 (as opposed to a table is full message or a signal 25/SIGXFSZ) usually means you're running Linux on a filesystem that doesn't support large file (ext2 for example), on a kernel too old to support large files, or a mysqld not compiled for large file support. Since your table is currently at least 10GB, I'm not sure why it just started complaing now :) The combination of your existing index plus this new one will bump the .MYI file over the 2GB point, but I don't think that should be a problem. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do i need an index for this?
Hmmw your syntax should ork. Possibly try CREATE INDEX urlindex ON internet_usage (time(8)); W 8hich will only use the first charaters of your time field, it should make the index significantly smaller in size. Though I'm not completly sure shorting time field this way is legal, but you can always try. J S wrote: Thanks for your reply. Can I just check my syntax is right for creating the key? CREATE INDEX urlindex ON internet_usage (time); I ran this before but got an error 27 (eventhough the filesize ulimit was set to unlimited). So just wondered if I was using the wrong syntax? JS. In the last episode (Jul 14), J S said: I want to find the earliest and latest times in the time column of my table internet_usage: +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | time | timestamp(14)| YES | | NULL| | +--+--+--+-+-+---+ So far there are 324936160 rows. If I do : SELECT MIN(time) as earliest, MAX(time) as latest from internet_usage; I can see the query is going to run for a long time. Do I have to create an index on time to speed this up or is there another way of doing it? Most definitely. An index will make that query almost instantaneous. Mysql won't even have to hit the table at all, and just has to look at the first and last index blocks. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do i need an index for this?
In the last episode (Jul 14), John Larsen said: Hmmw your syntax should ork. Possibly try CREATE INDEX urlindex ON internet_usage (time(8)); W 8hich will only use the first charaters of your time field, it should make the index significantly smaller in size. A timestamp field is only 4 bytes as it is, and I don't think you can index part of it. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: do i need an index for this?
In the last episode (Jul 14), J S said: Thanks for your reply. Can I just check my syntax is right for creating the key? CREATE INDEX urlindex ON internet_usage (time); I ran this before but got an error 27 (eventhough the filesize ulimit was set to unlimited). So just wondered if I was using the wrong syntax? Getting errno 27 (as opposed to a table is full message or a signal 25/SIGXFSZ) usually means you're running Linux on a filesystem that doesn't support large file (ext2 for example), on a kernel too old to support large files, or a mysqld not compiled for large file support. Since your table is currently at least 10GB, I'm not sure why it just started complaing now :) The combination of your existing index plus this new one will bump the .MYI file over the 2GB point, but I don't think that should be a problem. -- I am using AIX and the filesystem is large file enabled. I'm using the mysql max db binary downloaded off the mysql site as well. Do you know if that would have large file support compiled in? I don't know why it's complaining about the size because I'm running a backup of the database at the moment and that's already 20GB on the same filesystem. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
