How to limit a query to SHOW TABLES

Question

I have the following query:

SHOW TABLES LIKE '$prefix%' 

It works exactly the way I want, although I need pagination of the results. I tried:

 SHOW TABLES LIKE '$prefix%' ORDER BY Comment ASC LIMIT 0, 6 

I need him to return all tables with a specific prefix and order them according to their comment. I want to paginate using LIMIT with 6 results per page.

I'm obviously doing something very bad. How can I do that?

EDIT: I looked over this one . This did not work for me.

Answer 1

The above cannot be done via MySQL Syntax directly. MySQL does not support the LIMIT in certain SHOW operations. This is one of them. MySQL reference document .

The following will work if your MySQL user has access to the INFORMATION_SCHEMA database.

 SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DATABASE_TO SEARCH_HERE' AND TABLE_NAME LIKE "table_here%" LIMIT 0,5; 

Answer 2

Just select a standard query instead of using SHOW TABLES. for instance

 select table_name from information_schema.tables 

Then you can use things like ASC and LIMIT, etc.