Optimal LIKE search in SQL

SELECT NeededFields FROM Parts WHERE PartNumber LIKE 'ML%'

CREATE INDEX partno_idx ON parts (SUBSTRING(partnumber,0,1))

SELECT top 100 NeededFields FROM Parts WHERE PartNumber LIKE '%'
SELECT top 100 NeededFields FROM Parts WHERE PartNumber LIKE 'M%'
SELECT top 100 NeededFields FROM Parts WHERE PartNumber LIKE 'ML%'
SELECT top 100 NeededFields FROM Parts WHERE PartNumber LIKE 'ML0833%'

CREATE INDEX index_zones_name ON Zones(name);

EXPLAIN SELECT id FROM Zones WHERE name = 'toto.fr';
                              QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using index_zones_name on zones  (cost=0.00..21.06 rows=4 width=4)
   Index Cond: (name = 'toto.fr'::text)

EXPLAIN SELECT id FROM Zones WHERE name LIKE 'toto%';
                   QUERY PLAN
---------------------------------------------------------
 Seq Scan on zones  (cost=0.00..75991.43 rows=1 width=4)
   Filter: (name ~~ 'toto%'::text)

mysql> CREATE INDEX part_of_name ON customer (name(10));