dongqianzong4275 2019-04-02 14:47
浏览 88
已采纳

性能:使用try(语句)和catch(输出错误)而不是检查电子邮件是否已存在

Very often i need to prevent querys to get executed when a value like a email already exist.

Until now i searched for the value like that:

$checkemailexist = $X['db']->prepare("SELECT uid FROM userdata WHERE uid = :uid LIMIT 1");
$checkemailexist->execute(array(
':uid'=>$uid
));

if(empty($checkemailexist)){
   INSERT QUERY ..
}

...

The problem on a big database with many rows, a string search even on a varchar can take a lot of performance and time.

So i made the uid column unique and tried something like that:

try{    
    $insertuser = $X['dbh']->prepare("
    INSERT INTO user (uid) VALUES (:uid) 
    ");

    $insertuser->execute(array(
    ':uid'=> $mail
    ));

} catch (PDOException $e) {
        header("Location: ...");
        exit(); 
}


Its working fine, but could the performance even be worse ?

  • 写回答

1条回答 默认 最新

  • dongzhuo1498 2019-04-02 14:56
    关注

    After making uid column an [unique] index, you made all your queries faster. Both queries, either SELECT or INSERT will have to check the index, and it will take them both the same time to perform.

    Adding an index to the column used for search for is the real answer to your question. As to whether to use a select query or to catch an exception during insert is a matter of taste.

    However, your second example is rather wrong. You shouldn't handle every PDOException the same way but only a specific exception related to this very case, as it's shown in my PDO tutorial.

    The best way would be to keep the unique index but add a keyword IGNORE to the query and then check the number of affected rows

    $insertuser = $X['dbh']->prepare("INSERT IGNORE INTO user (uid) VALUES (:uid)");
    $insertuser->execute(['uid'=> $mail]));
    if (!$insertuser->numRows()) {
        header("Location: ...");
        exit(); 
    } 
    

    adding IGNORE would suppress the unique index error, and you will be able to check whether such a value already exists by simply checking the number of affected rows

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题