Why cant i FORCE INDEX on delete statements?

[VBAssassin]

Im confused now,

I can use FORCE IDNEX (`index_name`) on SELECT & UPDATE statements that use WHERE... but i cant use it for DELETE statements that use WHERE? Why not?

Using a timed test iv created a table with 100,000 records, and using this:

DELETE FROM `table` WHERE 'username' LIKE 'VBAssassin'

will result in 0.1 seconds without an index on username, but will result in just 0.0006 seconds with an index on username yet i cant use FORCE INDEX? Even when it will use an index of its choice is there any way to explicitly tell it which index to use?

Best regards,
scott

PS: thanks in advance to anyone who helps :-)

[raf]

how did you time this?

because the first time you run a delete/udate/select statement, it'll mostly take longer then the second time that you run the identical statement.

also, it's not realy standard practice to force using indexes if you're using the LIKE operator. In fact, i would be very surprised if the rdbm would even consider using an index to evaluate the like-clause. I don't see any way to maintain an index tha could handle wildcards.
with a like-operater, the rdbm will probably always do a complete table-scan (unless your where clause contains additional conditions on indexed columns, of course)

[VBAssassin]

how did you time this?

because the first time you run a delete/udate/select statement, it'll mostly take longer then the second time that you run the identical statement.

also, it's not realy standard practice to force using indexes if you're using the LIKE operator. In fact, i would be very surprised if the rdbm would even consider using an index to evaluate the like-clause. I don't see any way to maintain an index tha could handle wildcards.
with a like-operater, the rdbm will probably always do a complete table-scan (unless your where clause contains additional conditions on indexed columns, of course)


because the first time you run a delete/udate/select statement, it'll mostly take longer then the second time that you run the identical statement.

thats true. But dont worry i took that into account. I ran the delete using phpMyAdmin which tells you how long it took to run the query. With 100,000 records it took 0.1 seconds without an index on username, but will result in just 0.0006 seconds with an index on username (according that no records match the query in order to delete them). I also tested this 3 times to be sure on the times.


with a like-operater, the rdbm will probably always do a complete table-scan

Thats kinda wrong, use EXPLAIN and you can see which index's the database uses.


(unless your where clause contains additional conditions on indexed columns, of course)

Thats true tho lol, unless you use AND... i had to create a multiple column table along with force index to get my AND ones to use an index instead of a table scan! Which is really annoying!


I don't see any way to maintain an index tha could handle wildcards.

there isnt, unless the wildcards are AFTER the first character or digit for example:

dhfjhfd% = would use an index on that column,
%fhjshdfj% = wouldnt use an index because simply impossible to do so like that

instead its better to use a server side language like PHP to build a table of words and then create an index on that table of words (look through phpBB for some other open source bulletin board for examples of how they do it).