natasha_sharma

Anyone?

Barand, Impressive attention to detail. actually that qts was not about Quotes. Hence, It was not my focus and i turned a blind eye on that. However, great learning for me to give attention to details. Thankyou

Thank you Mr. Mod. You answered it.

I have a table mytable, there are 5 text variables that i want to do full text index on. So that i can use any of these variables as and when i need. Sometimes I want to use all these 5 Text variables AND sometimes I just want to use any one or two of these for Full text searching. I am using below Query: ALTER TABLE mytable ADD FULLTEXT INDEX my_fts (text1, text2, text3, text4, text5); Usage: select MATCH(text1, text3 ) AGAINST ( ' blue widgets ' IN BOOLEAN MODE) as score from mytable; Error: "Can not find index matching Index columns" but when i make query like this: select MATCH(text1, text2, text3, text4, text5 ) AGAINST ( ' blue widgets ' IN BOOLEAN MODE) as score from mytable; There is not error. It works fine. 1) SO, Can I just not use One single or two Text variables for Full Text Searching? 2) If no, then what is the solution? If i want to keep full text index on all 5 variables, but want to search Against any one of them?

I am using mysql statement in some other software using odbc. value = odbcconnectSQL("select MATCH(product_group) AGAINST ( ' +shrimp' IN BOOLEAN MODE) as score from company_info WHERE isin='abcd' AND MATCH(industry, product_group, products, raw_material, business_desc, gisc_ind_2) AGAINST ( ' +shrimp' IN BOOLEAN MODE) "); Problem is I want to use below keyword instead of +shrimp: "+shrimp +(export OR import) " Now the problem is, I am not able to use the Double quotes in this sql statement. How to fix that?

Hi, I used below code to create Fulltext search index on my table: ALTER TABLE company_info ADD FULLTEXT INDEX comp (industry, product_group, products, raw_material, business_desc, gisc_ind_2) I want to calculate score using only product_group variable. So i am using below query: select MATCH(product_group) AGAINST ( ' +shrimp' IN BOOLEAN MODE) as score from company_info WHERE isin='abcd' AND MATCH(industry, product_group, products, raw_material, business_desc, gisc_ind_2) AGAINST ( ' +shrimp' IN BOOLEAN MODE) Error: mariaDB "can not find fulltext index matching the column list" I know I create fulltext index on multiple columns, so i can use any column i want in future. But right now I want to calculate score variable using "product_group" variable only. How Can i do that?

Anyone Please???

Bytheway, disabling keys and enabling post update/inset seem sensible solution? ALTER TABLE table_name DISABLE KEYS; BEGIN; ... inserting data with INSERT or LOAD DATA .... COMMIT; ALTER TABLE table_name ENABLE KEYS;

Okey I will post Schema today. Also, I am not a coder, i hired someone at freelancer. I have basic understanding of DB design, but you guys are expert so I think i wll have to revisit the design now.

Correct, Index will get updated, which i am well aware. But my contention is Performance and not index getting updated automatically. Having many indexes on a table will slowdown the UPDATE & INSERT into the table. (if you re-read my posts above, i have explained that my main table is being updated with 4 other tables using different column combinations. Also the main table gets updated with at least 5000 New records every day. So there is UPDATE and INSERT happening everyday.

Dear QOC, I just ran this query in PhpMyAdmin: CREATE FULLTEXT INDEX corp_nz ON corp_news(content ASC, subject ASC) Earlier script has nothing to do with this table.

Actually, I ran this query: CREATE FULLTEXT INDEX corp_nz ON corp_news(content ASC, subject ASC)

The reason why i am adding-removing index on every run is, my master table (table1) is being updated (joined) by table2, table 3, table 4, table5 all in same code with different keys. If i make one time index then i need almost 6-7 variables in index in TABLE1. Also, this code UPDATES TABLE1 and if the index has that many variables then they get refreshed and slowdown the update process. That's my understanding... Is this wrong understanding?

1) I am using XAMPP. MySQL Server version: 10.1.9-MariaDB Table corp_news has around 10,000 rows. I am using below query to create Index using PHPMyADmin. ALTER TABLE corp_news ADD FULLTEXT INDEX `corp_nz` (`content` ASC, `subject` ASC); After running this query, On left hand side menu, when i click the "index" from the tree i see many indexes named like content2 content3 content4 . . . . content37 subject2 subject3 subject4 . . . . subject36 subject37 I thought it would create only one Index table named "corp_nz". what the heck so many index tables about? Is this normal? 2) If this is the case, how can i even drop the full text index on "corp_news" table?

I need help and Guidance. I am using XAMPP local server. I tried to create a Full Text search index using below command: ALTER TABLE corp_news ADD FULLTEXT INDEX `corp_nz` (`content` ASC, `subject` ASC); The table corp_news has around 91000 records with 7 columns. When I fire this query, in phpmyadmin, i see a popup message saying "mysqld.exe is not working" Surprisingly,when i run the same query on a subset of corp_news table with only 10,000 records, the there is no Error message, It work just fine. May someone please Please help me through this? My Server config is as below: Server version: 10.1.9-MariaDB key_buffer_size=16777216 read_buffer_size=262144 max_used_connections=2 max_threads=1001 thread_count=2 The MySQL Error log is: 2016-03-17 1:06:17 5088 [Note] InnoDB: Dumping buffer pool(s) not yet started 2016-03-17 1:06:17 5604 [Note] Plugin 'FEEDBACK' is disabled. 2016-03-17 1:06:17 5604 [Note] Server socket created on IP: '::'. 2016-03-17 1:06:17 5604 [ERROR] mysqld.exe: Table '.\mysql\db' is marked as crashed and should be repaired 2016-03-17 1:06:17 5604 [Warning] Checking table: '.\mysql\db' 2016-03-17 1:06:17 5604 [ERROR] mysql.db: 1 client is using or hasn't closed the table properly 2016-03-17 1:06:17 5604 [ERROR] mysqld.exe: Table '.\mysql\tables_priv' is marked as crashed and should be repaired 2016-03-17 1:06:17 5604 [Warning] Checking table: '.\mysql\tables_priv' 2016-03-17 1:06:17 5604 [ERROR] mysql.tables_priv: 1 client is using or hasn't closed the table properly 2016-03-17 1:06:17 5604 [ERROR] mysqld.exe: Table '.\mysql\columns_priv' is marked as crashed and should be repaired 2016-03-17 1:06:17 5604 [Warning] Checking table: '.\mysql\columns_priv' 2016-03-17 1:06:17 5604 [ERROR] mysql.columns_priv: 1 client is using or hasn't closed the table properly 2016-03-17 1:06:17 5604 [ERROR] mysqld.exe: Table '.\mysql\procs_priv' is marked as crashed and should be repaired 2016-03-17 1:06:17 5604 [Warning] Checking table: '.\mysql\procs_priv' 2016-03-17 1:06:17 5604 [ERROR] mysql.procs_priv: 1 client is using or hasn't closed the table properly 2016-03-17 1:06:17 5604 [Note] c:\xampp\mysql\bin\mysqld.exe: ready for connections. Version: '10.1.9-MariaDB' socket: '' port: 3306 mariadb.org binary distribution 2016-03-17 1:07:48 4904 [Note] InnoDB: Online DDL : Start 2016-03-17 1:07:48 4904 [Note] InnoDB: Online DDL : Start reading clustered index of the table and create temporary files 2016-03-17 1:07:48 4904 [Note] InnoDB: Online DDL : End of reading clustered index of the table and create temporary files 2016-03-17 1:07:49 4904 [Note] InnoDB: Online DDL : Completed 2016-03-17 1:46:13 7868 [Note] InnoDB: Online DDL : Start 2016-03-17 1:46:13 7868 [Note] InnoDB: Online DDL : Start reading clustered index of the table and create temporary files 2016-03-17 01:46:14 17a4 InnoDB: Assertion failure in thread 6052 in file row0merge.cc line 892 InnoDB: Failing assertion: b < &block[srv_sort_buf_size] InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 160317 1:46:14 [ERROR] mysqld got exception 0x80000003 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see http://kb.askmonty.org/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 10.1.9-MariaDB key_buffer_size=16777216 read_buffer_size=262144 max_used_connections=2 max_threads=1001 thread_count=2 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 787099 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... mysqld.exe!my_parameter_handler() mysqld.exe!my_mb_ctype_mb() mysqld.exe!?set_charset@String@@QAEXPBUcharset_info_st@@@Z() kernel32.dll!BaseThreadInitThunk() ntdll.dll!RtlInitializeExceptionChain() ntdll.dll!RtlInitializeExceptionChain() The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash.