Filmdatenbank

[BerndB]

Du warst auch nicht gemeint, die Suche über mehrere Sekunden meinte ich damit

Ich fand nur deine Aussage über VARCHAR lustig. Wo wollste du sonst drin speichern ? Text oder BLOB ?

Probier mal den FULLTEXT Index, da geht die POST ab.

Gruss

Bernd

 

[BerndB]

ch vermute das Stichwort lautet 'Match/Against'?

Ich könnte auch wieder auf die Volltextsuche umswitchen... Ich schätze mal in der Tabelle Title werden max. ne halbe Millionen Datensätze stehen... Was würdet ihr mir denn raten?

Brauchst ja nur den INDEX zu ändern und kann dann mal deine Suchanfrage starten.
Dann kann du entscheiden was besser ist

 

[akne123]

Ich habe nun auch mal 500.000 Testdaten in die Tabelle Title geladen, das Attribut title mittels FULLTEXT indiziert (Indextyp=Default).

SELECT * FROM title whWHERE title LIKE "%massa%; -> ca. 1 Sek. ohne FULLTEXT
SELECT * FROM title WHERE MATCH (title) AGAINST('massa'); -> ca. 2,5 Sek. mit FULLTEXT

Ich vermute ich habe etwas falsch gemacht oder?

 

[BerndB]

Poste doch bitte mal die Ausgaben von:

SHOW CREATE TABLE title;
SELECT VERSION();

 

[akne123]

CREATE TABLE `title` (
`title_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`timestamp` int(15) unsigned NOT NULL,
`description_de` varchar(2000) NOT NULL,
`year` int(10) unsigned NOT NULL,
`length` varchar(45) NOT NULL,
`image` varchar(500) NOT NULL,
`clicks` int(10) unsigned NOT NULL DEFAULT '0',
`description_en` varchar(2000) NOT NULL,
`imdbID` varchar(50) NOT NULL,
`trailer_de` varchar(255) NOT NULL,
`land` varchar(255) NOT NULL,
`imdb_rating` varchar(50) NOT NULL,
`trailer_en` varchar(255) NOT NULL,
`ref_title_id` int(10) unsigned NOT NULL,
`season` int(10) unsigned NOT NULL,
`episode` int(10) unsigned NOT NULL,
`releasedByAdmin` int(1) unsigned NOT NULL,
PRIMARY KEY (`title_id`) USING BTREE,
FULLTEXT KEY `fulltext` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=578685 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC



Version 5.6.11

 

[BerndB]

Merkwürdig, merkwürdig.

Bei mit dauert das 10 ms bei 2.600.000 Einträgen und auch normale MySQL DB nicht mal MariaDB die
noch mal schneller ist.

Server eventuell falsch konfiguriert ?

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.6.21-log |
+------------+
1 row in set (0,00 sec)

mysql> SELECT COUNT(*) FROM title;
+----------+
| COUNT(*) |
+----------+
|  2692294 |
+----------+
1 row in set (1,14 sec)

mysql> SELECT * FROM title WHERE MATCH (title) AGAINST('Sydney');
+----------+--------+-----------+----------------+------+--------+-------+--------+----------------+--------+------------+------+-------------+------------+--------------+--------+---------+-----------------+
| title_id | title  | timestamp | description_de | year | length | image | clicks | description_en | imdbID | trailer_de | land | imdb_rating | trailer_en | ref_title_id | season | episode | releasedByAdmin |
+----------+--------+-----------+----------------+------+--------+-------+--------+----------------+--------+------------+------+-------------+------------+--------------+--------+---------+-----------------+
|   578821 | Sydney |         0 |                |    0 |        |       |      0 |                |        |            |      |             |            |            0 |      0 |       0 |               0 |
|   582916 | Sydney |         0 |                |    0 |        |       |      0 |                |        |            |      |             |            |            0 |      0 |       0 |               0 |
...
...
...
|      |             |            |            0 |      0 |       0 |               0 |
|  3350360 | Sydney |         0 |                |    0 |        |       |      0 |                |        |            |      |             |            |            0 |      0 |       0 |               0 |
|  3354439 | Sydney |         0 |                |    0 |        |       |      0 |                |        |            |      |             |            |            0 |      0 |       0 |               0 |
+----------+--------+-----------+----------------+------+--------+-------+--------+----------------+--------+------------+------+-------------+------------+--------------+--------+---------+-----------------+
660 rows in set (0,01 sec)

mysql>

 

[BerndB]

Hier noch das EXPLAIN und das Profile vom Query

mysql> EXPLAIN SELECT * FROM title WHERE MATCH (title) AGAINST('Sydney');
+----+-------------+-------+----------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type     | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+----------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | title | fulltext | fulltext      | fulltext | 0       | NULL |    1 | Using where |
+----+-------------+-------+----------+---------------+----------+---------+------+------+-------------+
1 row in set (0,00 sec)

mysql>

+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+--------------+-------------+
| Status                         | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function       | Source_file  | Source_line |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+--------------+-------------+
| starting                       | 0.000018 | 0.000011 |   0.000008 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | NULL                  | NULL         |        NULL |
| Waiting for query cache lock   | 0.000004 | 0.000002 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | try_lock              | sql_cache.cc |         464 |
| init                           | 0.000002 | 0.000001 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | try_lock              | sql_cache.cc |         464 |
| checking query cache for query | 0.000005 | 0.000004 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | send_result_to_client | sql_cache.cc |        1594 |
| checking privileges on cached  | 0.000003 | 0.000002 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | send_result_to_client | sql_cache.cc |        1685 |
| checking permissions           | 0.000008 | 0.000007 |   0.000001 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | check_access          | sql_parse.cc |        5266 |
| sending cached result to clien | 0.000209 | 0.000013 |   0.000043 |                 3 |                   0 |            0 |             0 |             7 |                 0 |                 0 |                 0 |     0 | send_result_to_client | sql_cache.cc |        1796 |
| cleaning up                    | 0.000005 | 0.000003 |   0.000002 |                 0 |                   0 |            0 |             0 |             0 |                 0 |                 0 |                 0 |     0 | dispatch_command      | sql_parse.cc |        1772 |
+--------------------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+--------------+-------------+
8 rows in set, 1 warning (0,00 sec)

mysql>