Chris Mary

Sorry Paul...It is WHERE come always first..I wrongly said...

Since where comes first it ends with performance issue...I want to rotate the records with limit and finally apply where

LIMIT is applied first. Then the where condition will be applied in general.

Question:

I have 10,000 records and want to search for the string from 30th record to 40th record and match a particular string. the column is not a indexed one and want the where should be applied after fetching the records. So how should i do?

Hi,

I have decided to provide the search for DB search for full text and decided to use the following

Option#1 - Sphix
Option#2 - Solr
Option#3 - Elastic Search

Which one is very good out of the 3 as quality/performance is the primary criteria rather than the learning curve? Also stable and active development

Sphinx last release: Jul 09, 2021 . Not much active (http://sphinxsearch.com/bugs/my_view_page.php)
Solr last release and Solr UI : Active (https://issues.apache.org/jira/browse/SOLR-16463)
Elastic Search: Active and UI Kibana (https://www.elastic.co/blog/whats-new-elasticsearch-kibana-cloud-8-4-0)

Thanks

Thank you Paul Clapham for the clarification

You are absolutely fine Matthew Bendford. Need corrections when ever someone does a mistake. It was not intentional. i will follow the protocol/rules.

Like this

SELECT * FROM EMPLOYEE  
WHERE name like 'Cl%'  
ORDER BY name DESC  
LIMIT 1,2;  

But the question is where LIKE happens first and LIMIT happens next

Hi,

SELECT emp_name, emp_age, income FROM employees  
WHERE emp_age>30  
ORDER BY income DESC  
LIMIT 5;  

emp_age is not a indexed column.

so the question is LIMIT executed first on DB and later Where Condition is applied First on Non-Index Columns added

https://www.educba.com/mysql-offset/

Also It is possible to ADD offset with WHERE condition like below

SELECT emp_name, emp_age, income FROM employees  
WHERE emp_age>30  
ORDER BY income DESC  
LIMIT 5,10;  

Thanks

I found out a different solution to solve this via UNION which Paul Clapham highlighted and that was useful

Add SELECT CURDATE(), NOW() to all the queries.....

But now sure whether it returns the time on each query or time at the end....

Can someone clarify this?

Sure, Tim. I thought keeping as separate thread in general will not confused and make the people easy to focus on one at a time. Sorry if that causes a confusion.

Sure Matthew Bendford.

Thanks a lot Paul and Les.

Can you please share me a link with an example that would help?

So, what is your kind suggestion for the problem Tim?

Hi,

According to the link - https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html . we can add FULLTEXT index for a column for faster searching

I have 5 columns in a table and all of them are text/string columns which needs to be searched as LIKE operator and need quicker results

Shall i add FULLTEXT index for all the columns? Is it right to add index in all columns? Will it impact the DB performance or table performance

Thanks

Paul Clapham,

Assume my application server runs in 192.168.1.1
My DB server runs in 68.1.1.1

#1 - Is it possible to export via my application server and export the CSV in to my application server instead of in DB server? i want the file to be placed in application server

#2 - Is it possible to append the delta changes to export to CSV when needed?

How to process csv file after exported to make a faster search?

Thanks

Thank you Tim Holloway

Can you please kindly suggest a few links for each item in the below

1) which one as UI, so that user can query. so that i will import the DB and start querying which has inbuilt user interface which doesn't need java or any existing web application server

2) integrate lucene with existing web application server, to import the data from DB and also manage delta when there are updates in the table.