[Solved] How can I remove everything past \"LIMIT\" from a query? (details inside)

EverSQL Database Performance Knowledge Base

How can I remove everything past \"LIMIT\" from a query? (details inside)

I got a few queries built dynamically by my scripts. They usually fit the following template:

SELECT ... 
FROM ... 
JOIN ... 
WHERE ... (lots of filters, search conditions, etc. here) ... 
ORDER BY (optional) ... 
LIMIT (optional) ... OFFSET (optional)

I want to remove the LIMIT and OFFSET parts from the query. I used

$sql_entitati = implode("LIMIT", explode("LIMIT", $sql_entitati, -1));

to do it but then it hit me: what if there's no LIMIT in the query and what if the only LIMIT is somewhere in the where clauses?

So my question to you is: How can I safely remove everything after the LIMIT key word, without screwing it up if there's no LIMIT and/or there's a "LIMIT" somewhere in the where clause? All this done in php.

A bit of an edit for clarity:

the algorithm i use:

$sql = implode("LIMIT", explode("LIMIT", $sql, -1));

Will work on 99% of the cases. The problem occurs when the "LIMIT" key word at the end is missing, AND there is "LIMIT" written somewhere in the conditions. for example:

 SELECT * FROM table WHERE bla = 'SPEED LIMIT' ORDER BY table.a

this is the problem i need to tackle.

Solved using the following algorithm (Credit to techfoobar):

    $p = strrpos($sql, "LIMIT");
    if($p !== false) {
        $q = strpos($sql, ")", $p);
        $r = strpos($sql, "'", $p);
        $s = strpos($sql, "\"", $p);
        if($q === false && $r === false && $s === false)
            $sql = substr($sql, 0, $p);
    }

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `table` ADD INDEX `table_idx_bla_a` (`bla`,`a`);
The optimized query:
SELECT
        * 
    FROM
        table 
    WHERE
        table.bla = 'SPEED LIMIT' 
    ORDER BY
        table.a

Related Articles



* original question posted on StackOverflow here.