CODEWITHSUNDEEP
mysqlindexing
user1697061
user1697061

Reputation: 265

mysql add key does not work

I have this query:

SELECT adressid, adressname FROM kino_adressen WHERE city ='Seattle'

I wanted to create an index like this

ALTER TABLE <tablename> ADD KEY index_abc(adressid, adressname(40))

But when I then check it by using:

 EXPLAIN SELECT adressid, adressname FROM kino_adressen WHERE city ='Seattle'

It says

type = ALL
possible keys = NULL
key = NULL 
...rows = 106

Can anyone give some piece of advice how to do this properly ?

// edit: Another problem I do not understand:

SELECT DISTINCT
titel,
regie,
darsteller,
filmbild,
kino_filme.filmid,
kino_filme.beschreibung,
fsk,
filmlaenge,
verleih,
sprachfassung
FROM
    kino_filme
LEFT JOIN kino_terminefilme ON (
    kino_terminefilme.filmid = kino_filme.filmid
)
LEFT JOIN kino_termine ON (
    kino_terminefilme.terminid = kino_termine.terminid
)
LEFT JOIN kino_kinos ON (
    kino_kinos.kinoid = kino_termine.kinoid
)
LEFT JOIN kino_adressen ON (
    kino_adressen.adressid = kino_kinos.adressid
)
WHERE
    kino_adressen.adressid = 32038

And the result is like:

Why is kino_termine not using any index ? I set it to PK while creating and even added an index afterwards, but none of those helped.

Upvotes: 0

Views: 154

Answers (1)

juergen d
juergen d

Reputation: 204884

You added an index on the address but use the city in the where clause. Add an index on the city then it will be used.

Upvotes: 1

Related Questions