Skip to main content

SQL query optimization

SELECT query

{EXPLAIN | DESCRIBE | DESC} 


We usually use this command to check to see if how many rows the query goes through. Especially for the INDEX column checking.

Search

LIKE '%key%' consumes more resource than LIKE 'key%'

Denormalisation

This database design helps to speed up the data reading. Because the SQL query no need to join data between tables.



Comments

Post a Comment

Popular posts from this blog

PHP json_encode return empty array [] instead of empty object {}

Problem: Get below array for example.  $status = array ( "message" => "error" , "club_id" => $_club_id , "status" => "1" , "membership_info" => array (), ); This array will be encoded in json format echo json_encode($status); This function return json: {"message":"error","club_id":275,"status":"1","membership_info": [] } Notice the empty array [], it is not consistent, it should be an empty object {} {"message":"error","club_id":275,"status":"1","membership_info": {} } The cause: This problem is caused by the called function array(), which yields an empty array [] Solution: There are 2 solutions for this issue: Solution 1: Call new stdClass instead of array(), stdClass generates an empty object {} Solution 2: The above solution is complex in case there are plenty of arr...
1 comment
Read more

Command Prompt VS 2010: Fix MSVCRT.lib(MSVCR100.dll) : error LNK2005: xxx already defined in LIBCMT.lib(xxx.obj)

One of my task is to use Command Prompt of Visual Studio 2010 to evaluate the assignments of students. This assignment is about pointer and memory management in C++. The students are required to use new and delete operator in C++ to manage the memory and they need to ensure that the number of new callings must be equal to the number of delete callings. I overload the new and delete operator, build it into a static library (.lib file) and require student to use this .lib file when implementing the assignment. There is no problem when building the .lib file in VS 2010 project, but there are errors when compile the source code if I link the source code to the above static .lib file. Here is the format of error: MSVCRT.lib(MSVCR100.dll) : error LNK2005: xxx already defined in LIBCMT.lib(xxx.obj) Fortunately, I found 2 ways to fix this error on the internet. The first one is to remove the default lib when compiling the source code, it's libcmt.lib, because this libcmt.lib alread...
Post a Comment
Read more

Jenkins: SSH connect with username and password to AWS Linux instance

Jenkins  2.46.1 does not support to have a ssh connection with default pem file from aws. To do the automatically deployment from Jenkins server, a ssh connection should be establish from Jenkins server to the instance. These are steps to do to enable the SSH connection: AWS instance: Create a new user on the instance Edit the SSH config file to enable Password authentication for SSH and especially, specify some special encryption algorithms that supports Jenkins  File: /etc/ssh/sshd_config Put this at the end of the file KexAlgorithms diffie-hellman-group1-sha1,curve25519-sha256@libssh.org,ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group14-sha1 Use the new created user for Jenkins  
Post a Comment
Read more