DIFFERENT RESULTS FROM SAME QUERY, SAME STRUCTURE AND INDEXES
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