MySQL Forums
Forum List  »  Full-Text Search

DIFFERENT RESULTS FROM SAME QUERY, SAME STRUCTURE AND INDEXES
Posted by: Harold Stanley Rivas Martinez
Date: January 15, 2008 12:21PM

Hi every one.
I have this structure

CREATE TABLE `hunters` (
`hunter_id` bigint(11) NOT NULL auto_increment,
`hunter_fecha_ingreso` date default NULL,
`hunter_nombre` varchar(100) default NULL,
`hunter_fecha_nacimiento` date default NULL,
`sexo_id` int(11) default NULL,
`hunter_direccion` varchar(255) default NULL,
`hunter_telefono` int(11) default NULL,
`hunter_telefono2` char(10) default NULL,
`patrono_id` int(11) default NULL,
`hunter_nombre_referencia` varchar(100) default NULL,
`hunter_direccion_referencia` varchar(255) default NULL,
`hunter_telefono_referencia` int(11) default NULL,
`hunter_isss` char(20) default NULL,
`hunter_salario` decimal(11,2) default NULL,
`hunter_dui` char(20) default NULL,
`hunter_nit` char(20) default NULL,
`hunter_licencia` char(20) default NULL,
`estado_civil_id` int(11) default NULL,
`departamento_id` int(11) default NULL,
`municipio_id` int(11) default NULL,
PRIMARY KEY (`hunter_id`),
KEY `patrono` (`patrono_id`),
KEY `depto` (`departamento_id`),
KEY `municipio` (`municipio_id`),
FULLTEXT KEY `todos` (`hunter_nombre`,`hunter_direccion`,`hunter_isss`,`hunter_dui`,`hunter_nit`,`hunter_licencia`)
) ENGINE=MyISAM AUTO_INCREMENT=12272558 DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED

i have 10 Millions of records with names, address, and other data, when i search a name for example 'CARLOS RIVAS' in the table win 10 M or records, i receive the exact phrase 'CARLOS RIVAS' with a pour score and this name RIVAS RIVAS CARLOS EDWIN receive the best score. but surprise, i've created a copy of the table, without records, and i added as 20 records includind CARLOS RIVAS, RIVAS CARLOS and RIVAS RIVAS CARLOS EDWIN, what was the results?
i receive the data begginin from CARLOS RIVAS, RIVAS CARLOS and the last of coincidences was RIVAS RIVAS CARLOS EDWIN with a slow score.
So, what is the problem with this?
The query i used is the same in both cases:

SELECT a.hunter_nombre,
MATCH (a.hunter_nombre,a.hunter_direccion,a.hunter_dui,a.hunter_isss,a.hunter_licencia,a.hunter_nit)
AGAINST ('Carlos Rivas') as puntuacion
FROM hunters a
WHERE MATCH (a.hunter_nombre,a.hunter_direccion,a.hunter_dui,a.hunter_isss,a.hunter_licencia,a.hunter_nit)
AGAINST ('Carlos Rivas')

what can i do to fix my problem? when i search 'CARLOS RIVAS' i expect from firsts results all the names with just CARLOS RIVAS or RIVAS CARLOS, after the next coincidences if i search 'HAROLD STANLEY RIVAS' the firsts records must to be:
HAROLD STANLEY RIVAS
STANLEY HAROLD RIVAS
RIVAS HAROLD STANLEY
etc.

Help me please and thank you very much

Options: ReplyQuote


Subject
Views
Written By
Posted
DIFFERENT RESULTS FROM SAME QUERY, SAME STRUCTURE AND INDEXES
6402
January 15, 2008 12:21PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.