MySQL indexeren van kolommen en WHERE clause

[Verwijderd]

Hoi,

Ik probeer een query sneller te krijgen door de columns die ik in m'n WHERE clause heb staan te indexeren. Daarbij heb ik een aantal probleempjes:

- ik wil een zoekfunctie op 2 columns toepassen, op bijv. Firstname en Lastname: "SELECT Fields FROM Users WHERE Firstname LIKE 'string%' OR Lastname LIKE 'string%'". Ik heb beide columns zowel tezamen als individueel geindexeerd, maar de query duurt ca. 4 secs (zonder indexering ca. 6 secs). De MySQL documentatie geeft aan dat de index niet gebruikt wordt in een WHERE clause met OR op 2 verschillende columns (5.4.5 Multiple Column Indexes). Is er een andere manier om toch op 2 columns via OR te zoeken en toch gebruik te maken van indexes?

- wanneer ik de kolom Lastname indexeer en vervolgens een query uitvoer a la "SELECT * FROM Users WHERE Lastname LIKE 'string%'" dan duurt m'n query ca. 0.02 secs terwijl een een index op bijv. Firstname en dan een query a la "SELECT * FROM Users WHERE Firstname LIKE 'string%'" een duur heeft van ca 2 secs. Het zijn precies dezelfde column types, staan in dezelfde tabel, indextype is ook hetzelfde, aantal results ook hetzelfde....

- wat ik niet begrijp uit de documentatie zijn termen als "index prefix" en "...any index that doesn't span all AND levels in the WHERE clause is not used..." ... ze laten OR dus al helemaal buiten beschouwing?? Het komt toch in de praktijk heel vaak voor dat je in meerdere columns wilt zoeken naar een string (search engines) dus moet daar toch een oplossing voor zijn??

-- Jeroen

[Verwijderd]

Verwijderd schreef op 01 augustus 2002 @ 16:16:
Hoi,

Ik probeer een query sneller te krijgen door de columns die ik in m'n WHERE clause heb staan te indexeren. Daarbij heb ik een aantal probleempjes:

- ik wil een zoekfunctie op 2 columns toepassen, op bijv. Firstname en Lastname: "SELECT Fields FROM Users WHERE Firstname LIKE 'string%' OR Lastname LIKE 'string%'". Ik heb beide columns zowel tezamen als individueel geindexeerd, maar de query duurt ca. 4 secs (zonder indexering ca. 6 secs). De MySQL documentatie geeft aan dat de index niet gebruikt wordt in een WHERE clause met OR op 2 verschillende columns (5.4.5 Multiple Column Indexes). Is er een andere manier om toch op 2 columns via OR te zoeken en toch gebruik te maken van indexes?

Ik zou de index op beide kolommen tezamen weghalen, want die wordt niet gebruikt in de query zoals hierboven beschreven staat.

edit:
De 2 single column indexen worden dus als het goed is wel gebruikt.

- wanneer ik de kolom Lastname indexeer en vervolgens een query uitvoer a la "SELECT * FROM Users WHERE Lastname LIKE 'string%'" dan duurt m'n query ca. 0.02 secs terwijl een een index op bijv. Firstname en dan een query a la "SELECT * FROM Users WHERE Firstname LIKE 'string%'" een duur heeft van ca 2 secs. Het zijn precies dezelfde column types, staan in dezelfde tabel, indextype is ook hetzelfde, aantal results ook hetzelfde....

De waarden in je kolom hebben ook een invloed op de snelheid. Als je namelijk veel van dezelfde waardes in een kolom hebt staan, of langere waardes in een kolom heb staan, maakt dat een verschil.

- wat ik niet begrijp uit de documentatie zijn termen als "index prefix" en "...any index that doesn't span all AND levels in the WHERE clause is not used..." ... ze laten OR dus al helemaal buiten beschouwing?? Het komt toch in de praktijk heel vaak voor dat je in meerdere columns wilt zoeken naar een string (search engines) dus moet daar toch een oplossing voor zijn??

-- Jeroen

Welke index prefix bedoel je? Als je datgeen bedoeld wat hier beschreven staat (http://www.mysql.com/doc/I/n/Indexes.html), dan wordt daar het volgende bedoeld.

In plaats van dat de hele string in die kolom wordt gebruikt om een index op te bouwen, wordt de eerste x caracters gebruikt. De index zal meestal kleiner en sneller zijn, maar wel meer resultaten geven. Denk aan een index op de volgende waardes: 'aap', 'aandrijfas', 'aandacht', 'blah'. Als je de index 2 tekens lang maakt, zul je dus maar 2 verschillende waardes in de index hebben: 'aa' met 3 resultaten en 'bl' met 1 resultaat.

Als je dit (http://www.mysql.com/doc/M/y/MySQL_indexes.html) gebruik van index prefix bedoelt, gaat het over weer wat anders. Daar gaat het namelijk over multi-colum indexes. Als je een multi-column index maakt, sla je alle voorkomende combinaties van waarden van de geindexeerde kolommen op. Stel dat je een tabel Persoon met de volgende kolommen hebt:

code:

ID Naam Leeftid
1  Jan     10
2  Piet     38
3  Piet     25

Als je een index maakt over Naam en Leeftijd, krijg je dus de volgende index:

code:

Jan-10 -> 1
Piet-38 -> 2
Piet-25 -> 3

Als je nu de volgende query doet:

code:

SELECT *
FROM Persoon
WHERE
   Naam = 'Piet'

Dan zal hij de index kunnen gebruiken, omdat Naam de eerste kolom is in de index.

Ook in de volgende query kan hij de index gebruiken, omdat Naam en Leeftijd allebei in de index staan.

code:

SELECT *
FROM Persoon
WHERE
   Naam = 'Piet' AND
   Leeftijd = 38

In deze query kan hij echter de index niet gebruiken, omdat je alleen op de tweede kolom uit de index filtert, en de eerste kolom uit de index niet gebruikt wordt. In de index staat de waarde van de kolom Naam als eerste.

code:

SELECT *
FROM Persoon
WHERE
   Leeftijd = 38

HTH

[ Voor 0% gewijzigd door Verwijderd op 01-08-2002 17:01 . Reden: aanvulling ]

[JSchut]

De MySQL documentatie geeft aan dat de index niet gebruikt wordt in een WHERE clause met OR op 2 verschillende columns (5.4.5 Multiple Column Indexes). Is er een andere manier om toch op 2 columns via OR te zoeken en toch gebruik te maken van indexes?

Blijkbaar niet dus

wanneer ik de kolom Lastname indexeer en vervolgens een query uitvoer a la "SELECT * FROM Users WHERE Lastname LIKE 'string%'" dan duurt m'n query ca. 0.02 secs terwijl een een index op bijv. Firstname en dan een query a la "SELECT * FROM Users WHERE Firstname LIKE 'string%'" een duur heeft van ca 2 secs. Het zijn precies dezelfde column types, staan in dezelfde tabel, indextype is ook hetzelfde, aantal results ook hetzelfde....

hoe zit het met het aantal characters in de kolommen ?

[ Voor 0% gewijzigd door JSchut op 01-08-2002 16:56 . Reden: iets vergeten |:( ]

[Verwijderd]

hoe zit het met het aantal characters in de kolommen ?

die heb ik op max 20 gezet voor beide kolommen.

[Verwijderd]

Ik zou de index op beide kolommen tezamen weghalen, want die wordt niet gebruikt in de query zoals hierboven beschreven staat.

Ja ik heb nu 2 losse indexes van 20 chars, en individueel werken ze wel degelijk in een select query, maar dus niet in een OR

Welke index prefix bedoel je? Als je datgeen bedoeld wat hier beschreven staat (http://www.mysql.com/doc/I/n/Indexes.html), dan wordt daar het volgende bedoeld.

Deze: "To be able to use an index, a prefix of the index must be used in every AND group"

In deze query kan hij echter de index niet gebruiken, omdat je alleen op de tweede kolom uit de index filtert, en de eerste kolom uit de index niet gebruikt wordt. In de index staat de waarde van de kolom Naam als eerste.
[code]
SELECT *
FROM Persoon
WHERE
Leeftijd = 38

OK, dat snap ik.

[Verwijderd]

Misschien als ik het anders verwoord wordt mijn probleem duidelijker. Hoe kan je een query opstellen die in meerdere kolommen zoekt naar een keyword en daarbij gebruikt maakt van indexes voor die kolommen waarin je zoekt (die dus in de WHERE clause staan)?

[Verwijderd]

Verwijderd schreef op 01 augustus 2002 @ 17:04:
[...]

Ja ik heb nu 2 losse indexes van 20 chars, en individueel werken ze wel degelijk in een select query, maar dus niet in een OR

/me leest opnieuw

Ah ik snap het nu, hoewel ik het wel merkwaardig vindt. Als je een index wil gebruiken moet de geindexeerde kolom in alle door OR gescheiden delen voorkomen. Ik denk dat dit theoretisch niet zou moeten, maar goed.

Je zou dus het volgende kunnen doen:

code:

SELECT Fields 
FROM Users 
WHERE 
   Firstname LIKE 'string%' AND Lastname LIKE '%' OR
   Firstname LIKE '%' AND Lastname LIKE 'string%'

Best wel ranzig, maar je zou beide index nu wel moeten gebruiken.

[...]

Deze: "To be able to use an index, a prefix of the index must be used in every AND group"

[...]

OK, dat snap ik.

Mooi. In mijn voorbeeld is de prefix of the index dus de kolom Naam. Als die niet gebruikt wordt, wordt de index niet gebruikt.

Enjoy

[ Voor 0% gewijzigd door Verwijderd op 01-08-2002 17:17 . Reden: typo ]

[Verwijderd]

code:

SELECT Fields 
FROM Users 
WHERE 
   Firstname LIKE 'string%' AND Lastname LIKE '%' OR
   Firstname LIKE '%' AND Lastname LIKE 'string%'

Hij wordt er nog steeds niet sneller op, zit nog op de ca 6 seconden. Verbaasd me niet zoveel aangezien ik in de mysql docs las: "MySQL also uses indexes for LIKE comparisons if the argument to LIKE is a constant string that doesn't start with a wildcard character".

[ Voor 0% gewijzigd door whoami op 01-08-2002 21:26 ]

[Verwijderd]

Verwijderd schreef op 01 augustus 2002 @ 17:31:
[...]

Hij wordt er nog steeds niet sneller op, zit nog op de ca 6 seconden. Verbaasd me niet zoveel aangezien ik in de mysql docs las: "MySQL also uses indexes for LIKE comparisons if the argument to LIKE is a constant string that doesn't start with a wildcard character".

Hmmmmz .... ja, da's weer zo, niet aan gedacht dat dat hier van toepassing was . Dan maar 2 losse queries handmatig aan elkaar nieten. Jammer dat MySQL geen UNION ondersteunt.

[Verwijderd]

hoe zoekt de search engine van dit forum dan in verschillende columns in verschillende tables...? zonder hulp van indexen? per tabel / kolom een nieuwe query? of draaien ze mysql 4.0 (wel union)?

[LuCarD]

Misschien kan je het anders proberen.....

code:

select 
  t1.firstname, t1.lastname
from 
  `table` as t1
left join 
   `table` as t2
on 
   t1.id =t2.id
where 
    t1.firstname like 'string%'
or 
    t2.lastname like 'string%'

Kan je deze querie ook runnen met EXPLAIN er voor?

[whoami]

jes303 schreef op 01 augustus 2002 @ 17:31:

code:

SELECT Fields 
FROM Users 
WHERE 
   Firstname LIKE 'string%' AND Lastname LIKE '%' OR
   Firstname LIKE '%' AND Lastname LIKE 'string%'

Hij wordt er nog steeds niet sneller op, zit nog op de ca 6 seconden. Verbaasd me niet zoveel aangezien ik in de mysql docs las: "MySQL also uses indexes for LIKE comparisons if the argument to LIKE is a constant string that doesn't start with a wildcard character".

Ik denk dat die ..AND Lastname LIKE '%'.. etc zo een beetje overkill is hoor.
Als je nu eens doet:

code:

SELECT DISTINCT Fields 
FROM    Users
WHERE Firstname LIKE 'string%' OR Lastname LIKE 'string%'

[LuCarD]

whoami schreef op 01 augustus 2002 @ 21:27:
[...]

Ik denk dat die ..AND Lastname LIKE '%'.. etc zo een beetje overkill is hoor.
Als je nu eens doet:

code:

SELECT DISTINCT Fields 
FROM    Users
WHERE Firstname LIKE 'string%' OR Lastname LIKE 'string%'

Ermmm lees de post van MrX nogmaals door (deze topic 14579865#14579865 )