Hi Guys,
Im trying to ensure my DB's querys run as fast as possible.

However ive come across an issue where one table takes a LONG time to run a delete.

SO:
Whats the best thing to do.

Where should index's be put?

for example,
I have a table with 500,000 IP address's in it, However when i delete from this table i "delete from ips where ID = "123";

does this mean that i should index ID as this is what my querys are using as the WHERE.

As i understand it, It would be silly indexing any other column as they are never used against a query and would mean that any querys run on the table will slow down as it has to INDEX the other columns needlessly.

Any thoughts?

I suppose what im trying to get to is:
Do i only need to INDEX columns I use in WHERE clauses?

Google is being fairly helpful but just wondered what you guys thought?

Yes, you should index ID in this case.

The purpose of an index is to store the data in such a way that it allows for faster access. A linear search of 500,000, in the worst case, takes 500,000 comparisons. A tree search of the same data would require around 20 comparisons (log_2 500,000 ~ 19).

Indexes give you better search performance at the cost of memory; it takes additional memory to store the data in a btree or hash table. This being the case it is wasteful to index a column that isn't being used to lookup data.

Edit
Is it only delete queries that take a long time? If you run a select query with the same where clause does it also take a long time to process?
What is your primary key here? Can you post your table structure.

Cool,

Selects dont tend to take too long as they limit to 1000 results, Its just when 100k plus rows need to be deleted.

I have a primary key thats unique and have now created an index on ID.

This may speed things up in future ( cant test until its full again )

I did have a load of unrelated index's that someone else put in there and i think they were causing the delete issue.
I assume for every row that got deleted it then updated the index on all the other columns.

Not other querys use any other column as a where clause so hopefully a primary key and an index on id will speed things up in future.

May have to revive this thread if things dont speed up next time

Heres the structure of said table:

mysql> describe visitorips;
+-----------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ip | varchar(26) | NO | | NULL | |
| time | timestamp | NO | | CURRENT_TIMESTAMP | |
| siteid | varchar(30) | NO | MUL | NULL | |
| country | varchar(2) | NO | | NULL | |
| town | varchar(100) | NO | | Unknown | |
| lat | varchar(10) | NO | | Unknown | |
| lon | varchar(10) | NO | | Unknown | |
| extra | varchar(100) | NO | | NULL | |
| requested | int(1) | NO | | 0 | |
| ref | varchar(200) | NO | | NULL | |
| browser | varchar(100) | NO | | Unknown | |
+-----------+--------------+------+-----+-------------------+----------------+


I have a primary key on ID ( Cardinality of 299k at the moment )
and an index on SITEID ( this is used in where clauses ) Cardinality of 101 at the moment .

Indexes do increase the amount of time necessary to delete a row:
http://dev.mysql.com/doc/refman/5.0/...ete-speed.html

With an index on siteid however, it should take less time to find the rows that need to be deleted.

I have an unrelated suggestion:

Usually there is no reason why you should need to store an IP address as a string. Instead you could store it as an unsigned int. This will reduce the number of bytes required and reduce the cost of comparisons (numeric comparisons are cheaper than string comparisons).

Originally Posted by NullPointer

Indexes do increase the amount of time necessary to delete a row:
http://dev.mysql.com/doc/refman/5.0/...ete-speed.html

With an index on siteid however, it should take less time to find the rows that need to be deleted.

I have an unrelated suggestion:

Usually there is no reason why you should need to store an IP address as a string. Instead you could store it as an unsigned int. This will reduce the number of bytes required and reduce the cost of comparisons (numeric comparisons are cheaper than string comparisons).

Cool, Should speed things up a bit now ive removed the useless indexes.

Also, Wouldnt an unsigned int remove the dots and colons on ipv6 addresses? or is that what the unsigned bit does?

Thanks muchly Mr Pointer

I have noticed the selects are faster also now..

I wasn't thinking of IPv6 addresses. Converting an IPv4 address to an integer is fairly simple. Each section is an 8-bit integer (0-255) so all you have to do is drop the separator and append the bits to get a 32 bit integer. It has to be unsigned to store it as an INT in mysql otherwise you'll get an overflow. If you're using PHP see:
http://us2.php.net/manual/en/function.ip2long.php
http://us2.php.net/manual/en/function.long2ip.php

You can do something similar with ipv6 but you would have to use a bigint and converting it would be a bit more complicated. It would still reduce the cost of comparisons and decrease the amount of storage required. If storage isn't an issue and you aren't doing a lot of lookups in the ip column it may be too much trouble, otherwise this might help:
http://www.soucy.org/project/inet6/

Originally Posted by NullPointer

I wasn't thinking of IPv6 addresses. Converting an IPv4 address to an integer is fairly simple. Each section is an 8-bit integer (0-255) so all you have to do is drop the separator and append the bits to get a 32 bit integer. It has to be unsigned to store it as an INT in mysql otherwise you'll get an overflow. If you're using PHP see:
http://us2.php.net/manual/en/function.ip2long.php
http://us2.php.net/manual/en/function.long2ip.php

You can do something similar with ipv6 but you would have to use a bigint and converting it would be a bit more complicated. It would still reduce the cost of comparisons and decrease the amount of storage required.

Thanks mate,
Ill certainly look into it.

May write an optimised version of the files that use it and if the server starts getting slow for day to day usage, I could change to that.

Currently everything else is very speedy ( think ive optimised the php and DB to a point that it works well )

It was just this **** delete that would lock the whole system when deleting the rows from that table.

Oddly while it should only lock that one table, It seems to hang query's on other tables.

But this shouldnt ( touch wood ) be a problem anymore since these indexes have been fixed.


My optimisation path:
- Sort php files out to be leaner.
- Sort queries out to be less querys ( some pages used like 15 querys , now its down to 1 or two )
- Sort the indexes
- Move the DB to a separate server.
- Add more frontend servers to the system.
- Move the DB to another server and change the DB to run from memory / upgrade current server with like 16gig of ram.
- Cluster the databases.
- Cry