Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

Voller Tabellenscan trotz FULLTEXT INDEX

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von unhold, 2 Dezember 2016.

  1. unhold

    unhold Benutzer

    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: [​IMG]

    #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)
     
  2. akretschmer

    akretschmer Datenbank-Guru

    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!
     
    unhold gefällt das.
  3. unhold

    unhold Benutzer

    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
     
  4. akretschmer

    akretschmer Datenbank-Guru

    Bist Du festgelegt auf MySQL?
     
  5. unhold

    unhold Benutzer

    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?
     
  6. akretschmer

    akretschmer Datenbank-Guru

    Siehe meine Signatur ;-)
     
  7. unhold

    unhold Benutzer

    Hm, habe mit PG noch nie gearbeitet.

    Ich werde es mir mal ansehen. Vorab, kann ich da FULLTEXT auf Varchar anwenden?
     
  8. akretschmer

    akretschmer Datenbank-Guru

  9. unhold

    unhold Benutzer

    Das klingt gut, wenn ich nicht zu viel umstellen muss dann werde ich es vielleicht nutzen. Denke für den Tipp!
     
  10. akretschmer

    akretschmer Datenbank-Guru

    Fange gleich mit PostgreSQL 9.6 an, schon wegen der parallelen Ausführung von Scans, Joins und Aggregationen.
     
  11. unhold

    unhold Benutzer

    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.
     
  12. akretschmer

    akretschmer Datenbank-Guru

    Tja. Deine Entscheidung.
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden