ottimizzare la query mysql con l'operatore LIKE per i record 10k

Nota:

Quindi, come ho capito, il vero problema è dovuto alla clausola IN che sto usando per i tagid. Cambiare la parte della query per la ricerca del text non ha aiutato molto. Qualche idea su come migliorare la query?

La query impiega troppo tempo durante l'esecuzione sul server. Qui Partha S è un elemento di ricerca inserito dall'utente. I contatti della tabella contengono informazioni personali, i tag contengono il nome e l'ID della categoria; e la tabella contacts2tags contiene rispettivamente contactid e tagid con valori simili all'ID nei contatti e nei tag.

SELECT * FROM ( SELECT *, IF ( first_name LIKE 'Partha S' OR last_name LIKE 'Partha S' OR phone_number LIKE 'Partha S' OR mobile_number LIKE 'Partha S' OR email_address LIKE 'Partha S' OR address LIKE 'Partha S' OR organization LIKE 'Partha S' OR other LIKE 'Partha S' OR sector LIKE 'Partha S' OR designation LIKE 'Partha S' OR concat ( first_name, ' ', last_name ) LIKE 'Partha S' OR concat ( last_name, ' ', first_name ) LIKE 'Partha S', 1, 0 ) as exact, IF ( ( first_name LIKE '%Partha%' OR last_name LIKE '%Partha%' OR phone_number LIKE '%Partha%' OR mobile_number LIKE '%Partha%' OR email_address LIKE '%Partha%' OR address LIKE '%Partha%' OR organization LIKE '%Partha%' OR other LIKE '%Partha%' OR sector LIKE '%Partha%' OR designation LIKE '%Partha%' ) AND ( first_name LIKE '%S%' OR last_name LIKE '%S%' OR phone_number LIKE '%S%' OR mobile_number LIKE '%S%' OR email_address LIKE '%S%' OR address LIKE '%S%' OR organization LIKE '%S%' OR other LIKE '%S%' OR sector LIKE '%S%' OR designation LIKE '%S%' ) , 1, 0 ) as normal FROM contacts WHERE id in ( SELECT DISTINCT contacts.id from contacts INNER JOIN contacts2tags ON contacts.id = contacts2tags.contactid WHERE ( tagid in ( 178 ) ) ) ) d WHERE exact = 1 OR normal = 1 ORDER BY exact desc, last_name asc LIMIT 0, 20 

AGGIORNAMENTO: Come suggerito, ho rimosso l'operatore LIKE per la ricerca esatta e ho usato MATCH (..) AGAINST (..) invece di LIKE in quest'ultimo caso. Mentre la prima modifica ha migliorato leggermente le performance, ma l'utilizzo di MATCH () AGAINST () non ha modificato il tempo di esecuzione in modo sorprendente. Ecco la query aggiornata. PS Ho provato a utilizzare sia MATCH (tutti i cols) CONTRO (elemento di ricerca) e MATCH (single cols) CONTRO (elemento di ricerca) combinato con OR. Si prega di suggerire. Grazie

  SELECT * FROM ( SELECT *, IF ( first_name ='Partha S' OR last_name ='Partha S' OR phone_number ='Partha S' OR mobile_number ='Partha S' OR email_address = 'Partha S' OR address ='Partha S' OR organization ='Partha S' OR other ='Partha S' OR sector ='Partha S' OR designation ='Partha S' OR concat ( first_name, ' ', last_name ) ='Partha S' OR concat ( last_name, ' ', first_name ) ='Partha S', 1, 0 ) as exact, IF ( match(first_name,last_name,phone_number,mobile_number,email_address, address,organization,other,sector,designation) against( 'Partha') OR match(first_name,last_name,phone_number,mobile_number,email_address,address,organization,other,sector,designation) against( 'S') , 1, 0 ) as normal FROM contacts WHERE id in ( SELECT DISTINCT contacts.id from contacts INNER JOIN contacts2tags ON contacts.id = contacts2tags.contactid WHERE ( tagid in ( 178 ) ) ) ) d WHERE exact = 1 OR normal = 1 ORDER BY exact desc, last_name asc LIMIT 0, 20 

Un'ottimizzazione è che nel caso exact , non è necessario utilizzare LIKE (dovresti usarlo solo con il carattere jolly -%).

Un'altra cosa che puoi fare per rendere le cose più veloci è aggiungere un INDICE ai campi in cui stai andando a cercare.

Inoltre, solo se utilizzi MyISSAM come motore di archiviazione (per quella tabella) puoi utilizzare la ricerca full-text come questa

SELECT * FROM normal WHERE MATCH (titolo, corpo) CONTRO ('Queried_string')

 first_name LIKE '%S%' OR last_name LIKE '%S%' OR phone_number LIKE '%S%' OR mobile_number LIKE '%S%' OR email_address LIKE '%S%' OR address LIKE '%S%' OR organization LIKE '%S%' OR other LIKE '%S%' OR sector LIKE '%S%' OR designation LIKE '%S%' ) 

sembra stia apportndo pochissimo valore all'integer process.

Spero che questo ti aiuti.

Non sono solo tutte le LIKE , ma anche le OR . Anche per le condizioni che utilizzano LIKE , verranno comunque utilizzati gli indici. Quindi, per accelerare questa query, puoi creare un indice molto grande per tabella che combina tutti i campi in cui stai cercando.

Ma se vuoi davvero build una ricerca enginge, potresti prendere in considerazione l'utilizzo di Sphinx o ElasticSearch invece di query di mostri MySQL come questa.

Ti potrebbe piacere dare un'occhiata alle funzionalità match() e against() di MySQL.

Ecco alcuni esempi di codice dai loro documenti

 mysql> CREATE TABLE articles ( -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, -> title VARCHAR(200), -> body TEXT, -> FULLTEXT (title,body) -> ) ENGINE=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO articles (title,body) VALUES -> ('MySQL Tutorial','DBMS stands for DataBase ...'), -> ('How To Use MySQL Well','After you went through a ...'), -> ('Optimizing MySQL','In this tutorial we will show ...'), -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), -> ('MySQL vs. YourSQL','In the following database comparison ...'), -> ('MySQL Security','When configured properly, MySQL ...'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM articles -> WHERE MATCH (title,body) AGAINST ('database'); +----+-------------------+------------------------------------------+ | id | title | body | +----+-------------------+------------------------------------------+ | 5 | MySQL vs. YourSQL | In the following database comparison ... | | 1 | MySQL Tutorial | DBMS stands for DataBase ... | +----+-------------------+------------------------------------------+ 2 rows in set (0.00 sec) 

LEGGI DI PIÙ QUIhttp://dev.mysql.com/doc/refman/4.1/en/fulltext-natural-language.html