Voller Tabellenscan trotz FULLTEXT INDEX

U

unhold

Benutzer
Beiträge
8
Guten Morgen!

Vielleicht findet sich hier jemand, der mir bei einem Problemchen helfen kann.

Ich habe für die User-Verwaltung ein paar Tabellen welche alle mit der id verknüpft sind.

Besuche ich nun ein Benutzerprofil, werden die Daten über den Usernamen geholt.

SELECT bla FROM blub WHERE LOWER(username) = ?

Auf username liegt ein FULLTEXT-INDEX welcher aber so nicht genutzt wird, EXPLAIN zeigt mir einen vollen Tabellenscan an, das wäre später natürlich sehr doof wenn sich hunderttausende Einträge in der DB befänden.

Nun dachte ich mir, ich mach das ganze mit einer MATCH AGAINST-Abfrage, bin mir aber nicht sicher ob das ein guter Weg ist.

Ich habe auch etwas weiter gesucht und folgende Seite gefunden: Case-Insensitive Suche in SQL

Darin steht, dass die DB (verständlicherweise) über LOWER oder UPPER gar nicht auf den INDEX zugreifen kann. Weiter unten wird auch eine Option mitgegeben wie man das Problem umschiffen kann.


CREATE INDEX emp_up_name ON employees (UPPER(last_name))

Leider meldet phpMyAdmin dauernd:

CREATE INDEX lower_username ON blub (LOWER(username))



MySQL meldet:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'username))' at line 1

Ich bin mit dieser Methode, die mir besser als MATCH AGAINST zu sein scheint, nicht in der LAge einen "lower_username" INDEX anzulegen welcher später die WHERE LOWER(username) Abfragen bedient.

Ein Paar Details:

Datenbank-Server
Server-Typ: MariaDB
Server-Version: 10.2.2-MariaDB-1~xenial-log - mariadb.org binary distribution
Protokoll-Version: 10
Server-Zeichensatz: UTF-8 Unicode (utf8)
 
Werbung:
unhold schrieb:
CREATE INDEX lower_username ON blub (LOWER(username))
Zum Vergrößern anklicken....

Das ist absolut korrekt und funktioniert auch:

Code: 
test=# create table blub(bla int primary key, username text);
CREATE TABLE
test=*# set enable_seqscan to off;
SET
test=*# create index idx_blub on blub (lower(username));
CREATE INDEX
test=*# insert into blub values (27, 'aNdReAs');
INSERT 0 1
test=*# explain analyse select * from blub where lower(username) = lower('AnDrEaS');
  QUERY PLAN   
-----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on blub  (cost=4.20..13.68 rows=6 width=36) (actual time=0.028..0.029 rows=1 loops=1)
  Recheck Cond: (lower(username) = 'andreas'::text)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on idx_blub  (cost=0.00..4.20 rows=6 width=0) (actual time=0.019..0.019 rows=1 loops=1)
  Index Cond: (lower(username) = 'andreas'::text)
 Planning time: 2.448 ms
 Execution time: 0.071 ms
(7 Zeilen)

test=*#

Wie man sieht, er nutzt den Index (ich habe Seqscan ausgeschaltet, weil ansonsten die Tabelle zu klein wäre für einen Indexscan).

Also, fassen wir zusammen: das, was Du mit dem Index versucht hast, ist absolut richtig. Nur leider die Wahl Deiner Datenbank, MySQL und dessen Mutationen kann das nicht. Es kann keine funktionalen Indexe (das hier ist so einer), es kann keine partiellen Indexe, es kennt keine GIN, GiST, BRIN und weitere Indexe, es kann keine Check-Constraints, es kann keine Exlusion Constraint, es kann ... , ach, was solls, es kann faktisch nichts.

Kaffee!
 
Nichts? So rein gar nichts? Das is ja blöd... wie kann ich das Problem denn nun so lösen, dass die Datenbank den FULLTEXT INDEX bei einer solchen Abfrage nutzen kann? Die MACHT AGAINST funktioniert aber scheint mir nicht die beste Lösung zu sein.

Kaffee ist immer eine gute Sache :D
 
Ja, relativ.

Ich habe das Problemchen vorerst durch eine zweite Tabelle mit username gelöst wobei auf dem usernamen ein UNIQUE und FULLTEXT liegen. Es funktioniert...

Welche DB würdest du sonst empfehlen?
 
Na ja, ich habe ein recht großes Projekt und wenn ich da jetzt auf Postgre umsteige muss ich auch extrem viel in den PHP-Scripts umstellen.
 
Werbung:
Zurück
Oben