-2

MySQL Data - Best way to implement paging?

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

Pagination or Where Clause executed First. I am going to run this in huge Database

Thanks

Mark B
  • 183,023
  • 24
  • 297
  • 295
java beginner
  • 1
  • 1
  • @MarkB LIKE operation kills or hangs for long time. If LIMIT is applied first, i am happy to put the LIKE in a HUGE table – java beginner Oct 24 '22 at 17:10
  • You didn't include anything related to Java in your question, this is purely a MySQL database question, so I fixed your question tags. – Mark B Oct 24 '22 at 17:11
  • Add an index on name, and see if the long time is shorter. (Create index: `create index idx_name on SALES(name);`) – Luuk Oct 24 '22 at 17:20
  • Index doesn't work for LIKE. – java beginner Oct 24 '22 at 17:22
  • @javabeginner my reading indicates that index does work for `LIKE` if the only wildcard `%` character is at the very end of the like clause, which is what you have in your question. Otherwise, you would need to look into the full-text-search capabilities of MySQL. – Mark B Oct 24 '22 at 17:30
  • Yes want to do a full text-search however we cannot add the full text index to them. So that's the reason the above question comes. I want to loop through all the records of the table and do a full text search on a given column – java beginner Oct 24 '22 at 17:32
  • When doing `like 'Sl%' ` you do not need a full text index. When you really want to do `like '%Sl%' `, you should [edit] your question. – Luuk Oct 24 '22 at 17:35
  • @Luuk I may even do '%Sl% or '%a%' or 'sl%' - it can be of any search text – java beginner Oct 24 '22 at 17:37
  • @Luuk so what is the issue if i do something like this in sp - select * from (select * from emp LIMIT 0,10) where name like '%sp%' and similarly iterate through the end of the rows until i found out. Do you see an issue in this? – java beginner Oct 24 '22 at 18:07
  • 2 issues: 1) The inner sub-query needs to have an ORDER BY, 2) The complete query might return less than 10 rows. – Luuk Oct 24 '22 at 18:09
  • @Luuk The idea is i will loop them i.e incease the next would be 10,20 and iterate all the millions of records in the stored procedure – java beginner Oct 24 '22 at 18:11
  • When doing it that way, it will take more time than your original attempt... (Try: `SELECT * FROM SALES ORDER BY ? LIMIT 1000000,20;`, and you will see it is still slow) ((Replace the `?` by anything you want to order by ...)) – Luuk Oct 24 '22 at 18:12
  • @Luuk - https://stackoverflow.com/questions/12810346/alternative-to-using-limit-keyword-in-a-subquery-in-mysql => We can put a LIMIT and Iterate in stored procedure - assume a table has 1 million records i will put LIMIT from 0,1000 ad increase and find out the results quickly. If that's going to bring the results quicker, what would be the problem? – java beginner Oct 24 '22 at 18:16
  • Try it, and find out what problems you have with it. If you do not find any problems: Be happy! – Luuk Oct 24 '22 at 18:22
  • @Luuk please let me know if you foresee any issue, then i will be cautious as need to spend days on it – java beginner Oct 24 '22 at 18:27
  • 1
    Please "waste huge number of days" and learn from it. (This is in NO way meant negative...!, although it might sound that way) – Luuk Oct 24 '22 at 18:30
  • @Luuk - Do you suggest You Sphinx or Solr or ElasticSearch in this case - which works in both linux and windows for the full text search? – java beginner Oct 24 '22 at 18:49
  • Any comments @Luuk – java beginner Oct 24 '22 at 19:47

1 Answers1

2

Pagination or Where Clause executed First

The limit (pagination) always applies last. Otherwise the database would just be taking a few random records, and then attempting to apply your where clause to them and possibly returning no records at all from your query. That would not make any sense.

LIKE operation kills or hangs for long time. If LIMIT is applied first, i am happy to put the LIKE in a HUGE table

If your table is huge, then you need to make sure your where clause is always running against an index.

Mark B
  • 183,023
  • 24
  • 297
  • 295
  • So shall i find out the primary key of the table and add BETWEEN and put LIKE for a HUGE table. What would be the best practice if the table is HUGE in size and want to apply LIKE. Note Index cannot be done for LIKE – java beginner Oct 24 '22 at 17:20
  • No, you should create an index on the table that matches the where clause you are trying to execute. – Mark B Oct 24 '22 at 17:27
  • Like something '%cl%' wont't work. want to do a full text-search however we cannot add the full text index to them. So that's the reason the above question comes. I want to loop through all the records of the table and do a full text search on a given column – java beginner Oct 24 '22 at 17:32
  • That's more than what you described in your question. If you want to do a full-text search, on a huge database, your only option is to enable the full-text search index. If you can't do that, then you can't do a full text search. Use the correct tool for the job. – Mark B Oct 24 '22 at 17:38
  • Can we iterate all the rows with LIMIT in a subquery (0,10 and 10,20....goes on) and find the text with LIKE '%ghl%' and iterate all the rows in stored procedure – java beginner Oct 24 '22 at 18:00
  • No. You don't understand how Limit works, and you are also trying to completely re-invent full-text search. If if was as easy as throwing some subquery in your SQL then there would be no need for this whole full-text search indexing stuff. Full-text search is an extremely difficult problem to solve, and you aren't going to solve it by throwing some hacks into your SQL query. – Mark B Oct 24 '22 at 18:02
  • so what is the issue if i do something like this in sp - select * from (select * from emp LIMIT 0,10) where name like '%sp%' and similarly iterate through the end of the rows until i found out. Do you see an issue in this? – java beginner Oct 24 '22 at 18:06
  • Yes. I pointed out the issue with that in my original answer. You will get 10 random records from the table in the limit part, there's a good chance 0 of them will match the later `where` clause, so you will most likely get 0 results back from that entire query. Performing the `limit` **first** before applying the `where` clause makes **NO SENSE** if you actually care about getting any results back. – Mark B Oct 24 '22 at 18:11
  • https://stackoverflow.com/questions/12810346/alternative-to-using-limit-keyword-in-a-subquery-in-mysql => We can put a LIMIT and Iterate in stored procedure - assume a table has 1 million records i will put LIMIT from 0,1000 ad increase and find out the results quickly. If that's going to bring the results quicker, what would be the problem? – java beginner Oct 24 '22 at 18:16
  • You wouldn't be able to paginate through your entire result set that way. You would only ever be querying a small, random subset of the entire records in your database. It sounds like what you really want is some hacked up terrible solution instead of using the actual tools that are designed to solve the problem. It makes me wonder why you didn't just code that up then. Why ask here if you don't want to do things the correct way? – Mark B Oct 24 '22 at 18:24
  • i can write up all the codes in SP or via programming but wanted to know is that right way so that i don't waste huge number of days.... – java beginner Oct 24 '22 at 18:26
  • It is the wrong way, but you keep saying you want to do it that way anyway. The right way is to use a search engine, like Elasticsearch, or enable the full-text search index in MySQL. – Mark B Oct 24 '22 at 18:46
  • You are absolutely right Mark B. So which one is good to go - Sphinx or Solr or ElasticSearch in this case - which works in both linux and windows – java beginner Oct 24 '22 at 18:48
  • Any comment @Mark B – java beginner Oct 24 '22 at 19:47
  • Elasticsearch is the most modern, and popular of those choices. – Mark B Oct 24 '22 at 20:46
  • can you please share a good link where i can bind elastic search for mysql? also how to handle delta - create/update/delete happens in mysql – java beginner Oct 25 '22 at 03:37
  • I'm not aware of any way to "bind" Elasticsearch to MySQL or any other database. You have to treat Elasticsearch like a separate database and copy all create/update/delete actions to Elasticsearch. – Mark B Oct 25 '22 at 04:32