2

I know how to add a fulltext index and do basic boolean searches...

ALTER TABLE products ADD FULLTEXT KEY myIndex (model, description);
SELECT * 
FROM   products 
WHERE  MATCH(model, description) AGAINST('myKeyword' in boolean mode);

However, I am wondering if it's possible to do something like this...

I want my SQL to check if 'myIndex' exists, and if not it should automatically grab existing column names and add a fulltext index (notice the * in place of column names):

ALTER TABLE products ADD FULLTEXT KEY myIndex (*);

Also, when searching the index, can I again automatically grab column names, instead of having to enter them manually (again notice the * in place of column names)...

SELECT * 
FROM  products 
WHERE MATCH(*) AGAINST('myKeyword' in boolean mode);

I don't know the correct syntax, but something like this possible? I've been searching google for hours and am having trouble finding answers. I know I could get PHP to generate the SQL I need, but I am wondering if this is all possible using strictly SQL?

Thanks!

Namphibian
  • 12,046
  • 7
  • 46
  • 76
Jesse Leite
  • 6,581
  • 3
  • 18
  • 17
  • This answer by Bill Karwin would be the way to tackle the problem. However if I understand your question you are trying to find a way to dynamically index the database which is really not a good idea. – Namphibian Jul 16 '13 at 23:12
  • It would be very helpful to dynamically index the database in the case of this specific web app I am building :) What are the disadvantages to dynamically indexing? – Jesse Leite Jul 16 '13 at 23:37

2 Answers2

4

Based on your comment you want to dynamically index the database. This is generally not a good idea. Indexes cut both ways it is a double edged sword. Let me explain.

So essentially a index is a data structure on disk that contains all the values to be indexed. For example assuming you have a 1000 row table with 10 columns and you index 1 column this new index would contain 1000 entries as well. It will contain all the rows values for that column. This index is then written to disk so you can read it.

Next time you insert a new row it has to insert into the table and the index. When you update the indexed column it has to update both the table and the index. From what I can see in your question you want to dynamically index several columns.

So lets say you have a rather large table with 10 000 000 rows in and 3 columns and you index them all that is 30 000 000 values that needs to be indexed. Essentially when you dynamically create this index the server is going to be seriously slow while it indexes the table. Also once it has finished your inserts will be slower and updates to indexed columns would be slower. General rule of thumb indexes speed up reads and slow down inserts.

Now just to add a little more complexity. There is no way you can guarantee that MySQL will use the newly created index. MySQL uses internal statistics to decide which index to use. Though in your case this might be less of a problem since you are using full text indexes. You can force index usage in MySQL but that is also not a optimal way of doing it.

If I can make a suggestion don't optimize like this. You are trying to do a very general approach to optimization i.e. apply indexes everywhere. Rather enable the slow query log and identify the queries running slowly. Then use those queries together with the explain statement to identify how to optimize that situation. You will end up with less indexes and get a good read and write speed balance.

I hope this all makes a bit of sense.

Namphibian
  • 12,046
  • 7
  • 46
  • 76
  • Does make sense, thank you for your reply. I see what you mean about speed. However, I am building a CMS of sorts where my client is using my app to build table structure. The CMS needs fulltext search, so I want indexing to happen automatically as client adds fields to structure. Wait times should not be an issue due to small databases (max 500 rows). That said, I want to do this right. If I ever get the chance to work on larger databases, I do not want speed problems. How would you suggest I set up indexing as the user adds fields using my CMS? Thanks I appreciate your time! – Jesse Leite Jul 17 '13 at 17:02
  • 1
    Main advise if it aint broke dont fix it. Basically add indexes where and when needed. You do this by enabling the slow query log and then reviewing those queries by using the EXPLAIN command in MySQL to see how you optimize them. – Namphibian Jul 17 '13 at 21:59
  • +1 One cannot optimize for unknown future expansion. Optimizations like indexing are made to improve *specific query types*. You won't know which indexes to create until you know which queries are running and need optimization. See my presentation [How to Design Indexes, Really](http://www.slideshare.net/billkarwin/how-to-design-indexes-really). – Bill Karwin Jul 18 '13 at 16:43
2

You can check if the fulltext index exists this way:

SELECT DISTINCT index_name 
FROM INFORMATION_SCHEMA.STATISTICS
WHERE (table_schema, table_name) = ('mydatabase', 'products')
  AND index_type = 'FULLTEXT';

You can get the columns in that index this way:

SELECT column_name 
FROM INFORMATION_SCHEMA.STATISTICS
WHERE (table_schema, table_name) = ('mydatabase', 'products')
  AND index_type = 'FULLTEXT'
ORDER BY seq_in_index;

You can't use MATCH(*). The columns must be spelled out, you must name all the columns in the index, and you must name them in the same order you did when you defined the index.

I should also note that some queries against the information_schema seem slow, because InnoDB samples the tables and indexes by reading random pages from disk. That is, reading metadata causes I/O. You can mitigate this with SET GLOBAL innodb_stats_on_metadata=0. See also http://www.mysqlperformanceblog.com/2011/12/23/solving-information_schema-slowness/

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Great thanks Bill. I will play around with this more tomorrow morning! – Jesse Leite Jul 16 '13 at 23:39
  • If 'myIndex' fulltext index is already present, and I add a new column to table structure, is it possible to add that new column to existing 'myIndex'? Or do I need to `alter table products drop index myIndex;` , then re-add index? – Jesse Leite Jul 17 '13 at 17:06
  • You need to drop the index and re-add it, if you want to add a column. – Bill Karwin Jul 17 '13 at 17:06
  • Okay thank you! I just noticed your edit as well. I am using MyISAM engine, not InnoDB. I am not sure why I use MyISAM though :) Have to do some research on this. – Jesse Leite Jul 17 '13 at 17:18
  • No one should use MyISAM without having a specific, provable reason why. That said, one real reason why one would use MyISAM is that InnoDB doesn't support fulltext indexes. :-) The fulltext indexes in InnoDB in MySQL 5.6 are still not ready for production use, as far as I've tested them. – Bill Karwin Jul 17 '13 at 17:20
  • I see. If I need fulltext indexing, I guess I will stick with it. My reason was that MyISAM is the default engine in my MySQL installation :) If I am not using fulltext indexing, what are the advantages to InnoDB? – Jesse Leite Jul 17 '13 at 17:28
  • Many advantages. Try this: run an update on a MyISAM table that takes 5 seconds. Halfway through, hit Ctrl-C and kill the update. What happens? If you're lucky, some rows have been updated, some have not. If you're not lucky, the MyISAM table is corrupt. Whereas InnoDB assures that updates succeed fully, or else not at all (atomic changes). InnoDB also resists corruption in a crash. That alone should be enough of a reason to use InnoDB for any data you care about. – Bill Karwin Jul 17 '13 at 17:36
  • Hmm interesting, thank you. In this application, it will be probably 5% inserts & 95% reads. I am finding more on google about searching using `WHERE LIKE '%keyword%'`. So many options; So confusing! It's too bad fulltext in InnoDB isn't ready. – Jesse Leite Jul 17 '13 at 17:54
  • See my presentation [Full Text Search Throwdown](http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql) for more options. Using `'%keyword%'` is the worst option by orders of magnitude. – Bill Karwin Jul 17 '13 at 17:56
  • Great presentation! Good to know about `%keyword%`. I do like the option to customize relevancy with fulltext anyway. Thanks for your help again Bill! – Jesse Leite Jul 17 '13 at 18:09