Is there any way to make this code perform faster?

Published 3 months ago by behnampmdg3

Hello;

Is there any way to make this code perform faster? Or is it as optimized as it can be.

It gets inconsistent traffic, up to 50k requests per second. So need to make it as light as possible.

<?php
include 'config.php';

if(isset($_POST['user_id']) && ctype_digit($_POST['user_id']) && 
   isset($_POST['launch_id']) && ctype_digit($_POST['launch_id']) && 
    isset($_POST['secret_key']))
    {
        $conn = new PDO("mysql:host=$servername;dbname=$database", $username, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        //Check Credentials
        $stmt = $conn->prepare("SELECT secret_key, launch_timezone FROM launch_owners INNER JOIN launch_launches ON launch_launches.user_id = launch_owners.id WHERE launch_owners.id = :id AND secret_key = :secret_key AND launch_launches.id = :launch_id");
        $stmt->bindParam(':id', $_POST['user_id']);
        $stmt->bindParam(':secret_key', $_POST['secret_key']);
        $stmt->bindParam(':launch_id', $_POST['launch_id']);
        $stmt->execute();
        if($stmt->rowCount()==1)
            {
                $result = $stmt->fetchAll();
                date_default_timezone_set($result[0]['launch_timezone']);
                //Insert record
                    $stmt = $conn->prepare("INSERT INTO launch_temp_leads (user_id, launch_id, prospect_email, time_added, date_added, source) VALUES (:user_id, :launch_id)");
                    $stmt->bindParam(':user_id', $_POST['user_id']);
                    $stmt->bindParam(':launch_id', $_POST['secret_key']);
                    $stmt->bindParam(':prospect_email', $_POST['prospect_email']);
                    $stmt->bindParam(':time_added', date('i:H'));
                    $stmt->bindParam(':date_added', date('Y-m-d'));
                    $stmt->execute();
                //Insert record    
            }
        $conn=NULL; 
    }
else 
    {
        echo "Not sufficient post data";
    }   

Thanks

Cronix
Cronix
3 months ago (730,880 XP)

I don't see one line of code in there that pertains to laravel? It looks like just a generic script?

It looks ok. The main thing that would speed it up is properly indexing the relevant fields in the db if they aren't already. secret_key, launch_key and launch_launches.user_id should have indexes, maybe more but that's all I see from that one query. I assume all other id fields are primary keys and indexed already.

Not sure what you're going to do if if($stmt->rowCount()==1) is not == 1 though. You just kind of ignore that case.

behnampmdg3
if($stmt->rowCount()==1)

to validate the credentials. If it's trash, ignore it.

What kind of index for secret_key and launch_key? Unique?

CREATE TABLE `launch_temp_leads` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `date_added` date NOT NULL,
 `time_added` varchar(255) NOT NULL,
 `launch_id` int(11) NOT NULL,
 `prospect_email` varchar(255) NOT NULL,
 `source` varchar(255) NOT NULL,
 `user_id` int(11) NOT NULL,
 `status` varchar(255) DEFAULT 'pending',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
CREATE TABLE `launch_owners` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `account_type` varchar(255) NOT NULL,
 `secret_key` varchar(255) NOT NULL,
 `email` varchar(255) NOT NULL,
 `password` varchar(255) NOT NULL,
 `date_added` date NOT NULL,
 `status` varchar(255) NOT NULL,
 `timezone` varchar(255) NOT NULL,
 `temp_password` int(11) DEFAULT NULL,
 `password_update_request` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
Cronix
Cronix
3 months ago (730,880 XP)

Just a regular index. Whether they are unique indexes is up to you. I don't know your data or whether they should be unique or not.

I just mean that if the post data isn't adequate, you give a message...

echo "Not sufficient post data";

to validate the credentials. If it's trash, ignore it.

if($stmt->rowCount()==1)

but here (this isn't checking credential...it's seeing if there was a result from the query), you don't do anything if there isn't a result from the query. Just seems odd to return a message for the post data being inadequate, but return nothing if the result wasn't found.

behnampmdg3

Got it.

By the way cronix, this code works SO MUCH faster than Laravel or even Codeigniter.

I am starting to accept using a framework for end-point api is not a good idea.

NickVahalik

You're doing two queries. You could combine them into one by making your insert use a select:

INSERT INTO launch_temp_leads (user_id, launch_id, prospect_email, time_added, date_added, source)
SELECT launch_owners.id, secret_key, :prospect_email, now(), now(), :source
    FROM launch_owners 
    INNER JOIN launch_launches ON launch_launches.user_id = launch_owners.id 
    WHERE launch_owners.id = :id
        AND secret_key = :secret_key 
        AND launch_launches.id = :launch_id

Anyway, what this would do is do the insert if you can find the value in the source table. You could then see what the effected row count was to see if something was actually inserted. If it was, then everything was OK. If not... well then obviously it wasn't.

I'd also change your two date_added and time_addedfields down to a single timestamp column. You'd need to figure out how to add the date above. I wouldn't about adding TZ data in the fields for inserts... just format it properly when you are displaying as MySQL will store timestamps in UTC anyway.

An example of this working on a much simpler table:

CREATE TABLE test (
    id int unsigned not null auto_increment primary key,
    user_id int unsigned not null,
    something varchar(250)
);

CREATE TABLE users (
    id int unsigned not null auto_increment primary key,
    name varchar(20)
);

INSERT INTO users (name) VALUES ('user1'), ('user2'), ('user3'1);

-- Row inserts properly...
INSERT INTO test (user_id, something) SELECT id, 'blah' from users where name = "user1";

-- Nothing inserted.
INSERT INTO test (user_id, something) SELECT id, 'blah' from users where name = "user4";
Cronix
Cronix
3 months ago (730,880 XP)

By the way cronix, this code works SO MUCH faster than Laravel or even Codeigniter.

Are you trying to be Captain Obvious lol? Well yeah, procedural raw php will always be faster than any framework, except for maybe phalcon, which is a php extension written in C.

Why would you think a framework would be faster than procedural code? Think of all of the stuff a framework has to load and process vs something that only has exactly what it needs?

The thing is, frameworks have a ton of stuff that provides security (and have been tested by tens of thousands, if not hundreds of thousands of people and security experts), and makes it a lot faster to develop in than writing procedural code. It's not a problem to do it, if you really know your stuff and are up on best security practices. I don't think anyone has ever said a framework will be faster than just a single file with 0 libraries.

behnampmdg3

Thanks.

Nixck I understand what you mean.

The question is how much 'performance' difference we talking here?

This is currently responding 100% to 700 requests per second.

Peace

NickVahalik

You'd have to try it—but at least the overhead of one call—so it could be 25%-50% faster at least.

behnampmdg3

Interesting!

I won't be able to change the structure of the tables though, to be honest.

However, I am gonna look at mixing the queries and post it below.

Also, launch_id gets lost there in your query.

And finally, the timezone. Each launch has its own timezone. Your query doesn't seem to retrieve it.

I might store everything as UTC, hoping it looks after the Day Light Saving, and when I run the cron, convert from UTC to launch_timezone.

Thanks though, I hope we can fix it.

behnampmdg3

So far

$stmt = $conn->prepare("
            INSERT INTO launch_temp_leads 
            (user_id, 
             launch_id, 
             prospect_email, 
             time_added, 
             date_added, 
             source) 
SELECT launch_owners.id, 
       :launch_id, 
       :prospect_email, 
       :time_added, 
       :date_added, 
       :source 
FROM   launch_owners 
       INNER JOIN launch_launches 
               ON launch_launches.user_id = launch_owners.id
       AND launch_launches.id = :launch_id 
WHERE  launch_owners.id = :id 
       AND secret_key = :secret_key  ");
        $time = date('H:i');
        $date = date('Y-m-d');
        $stmt->bindParam(':id', $_POST['user_id']);
        $stmt->bindParam(':secret_key', $_POST['secret_key']);
        $stmt->bindParam(':launch_id', $_POST['launch_id']);
        $stmt->bindParam(':prospect_email', $_POST['prospect_email']);
        $stmt->bindParam(':time_added', $time);
        $stmt->bindParam(':date_added', $date);
        $stmt->bindParam(':source', $_POST['source']);
        $stmt->execute();

Please sign in or create an account to participate in this conversation.