niclas Posted February 10, 2012 Share Posted February 10, 2012 I'm using a query like this: SELECT firstname, lastname, mail, phone FROM clients WHERE company_id='$company_id' AND (MATCH(firstname,lastname,mail,phone,custom1,custom2,custom3) AGAINST ('$queryfixed' IN BOOLEAN MODE)) ORDER BY firstname, lastname, mail $queryfixed is the query changed from "sam jones" to "+sam* +jones*" Currently I have no full text index at all. All I have is normal indexes on mail, company_id and phone. This is a table of around half a million rows, but each company_id has around 500-2000 clients, and most queries executes fast. Is it correct that it works without any full text indexes at all? I tried making a full text index with firstname, lastname, and there was no big time difference when I run it on a single company. Users mostly search for first and last names, but sometimes also the custom fields. Should I make an index convering all columns that are in the MATCH(...) clause, or only on the most frequently used, like first/last name? I'm happy for any tips on how to optimize this, as the database grows! I'm using version: 5.0.90 Link to comment https://forums.phpfreaks.com/topic/256802-match-against-why-does-it-work-without-full-text-indexes/ Share on other sites More sharing options...
mikosiko Posted February 10, 2012 Share Posted February 10, 2012 Ref: http://dev.mysql.com/doc/refman/5.5/en/fulltext-restrictions.html The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE. Boolean-mode searches can be done on nonindexed columns, although they are likely to be slow. Link to comment https://forums.phpfreaks.com/topic/256802-match-against-why-does-it-work-without-full-text-indexes/#findComment-1316580 Share on other sites More sharing options...
niclas Posted February 10, 2012 Author Share Posted February 10, 2012 Interesting, thanks. So would it help if there is a fulltext index on (firstname,lastname) or does it still have to scan all rows? Seems logical that it would need to scan everything, since finding a match in any of those two wouldn't show all hits. Link to comment https://forums.phpfreaks.com/topic/256802-match-against-why-does-it-work-without-full-text-indexes/#findComment-1316698 Share on other sites More sharing options...
fenway Posted February 10, 2012 Share Posted February 10, 2012 It's silly to use it without an index. Link to comment https://forums.phpfreaks.com/topic/256802-match-against-why-does-it-work-without-full-text-indexes/#findComment-1316699 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.