To test query execution time – you will need to delete the internal caches of mysql to figure out the exact time the query needs to execute .
This can be done by the following command after each query execution : -
Flush Tables;
Post your trick
delete mysql cache while testing query performance
Using column_name in RegExp in MySQL
Wrong:
SELECT a.company, a.name, b.title, b.description, b.t_id
FROM a, b
WHERE ( b.title
REGEXP "[[:<:]]a.company[[:>:]]" OR b.description )Correct:
SELECT a.company, a.name, b.title, b.description, b.t_id
FROM a, b
WHERE
(
b.title REGEXP concat('[[:<:]]', a.company, '[[:>:]]') )Reason: In the wrong query a.company is in quote (’ ‘) so it will be consider as string. So this will be overcomed by concat function of mysql.
Regular Expressions in MYSQL (REGEXP)
In mysql I need to get the stores name starting from ‘a’. For that I can use LIKE
e.g. SELECT * FROM stores WHERE store_name LIKE ‘a%’;
But what if i want to get stores name starting from numeric (0-9). Here comes REGEXP. My query would be like this.
e.g.
SELECT * FROM stores WHERE store_name REGEXP ‘^[0-9]‘ ;
OR
SELECT * FROM stores WHERE store_name REGEXP ‘^[a]‘ ;
Where ^ operator represents the starting of line or text. See more info here.
MySQL FullText Searching
Suppose you want to search a keyword in 5 different columns or almost in all the fields of the table, then MySQL
provides us with fulltext indexing and searching method.
Before using this method , you need to add FullText to the columns in which you want to search.
ALTER TABLE member ADD FULLTEXT(firstname, lastname,address);
Then comes your main query :
SELECT firstname, lastname,address FROM member
WHERE MATCH (firstname,lastname,address) AGAINST (’test’);
The Match will look for “exact match” in the above 3 columns and on successfull match it will return that row.
Suppose in member table there is a an entry with firstname = test then the output will be :
firstname lastname address
test abc xyz
Suppose you want to use wildcards then modify your query :
SELECT firstname, lastname,address FROM member
WHERE MATCH (firstname,lastname,address) AGAINST (’*test*’);
How to use MySQL FOUND_ROWS()?
MySQL has a function called FOUND_ROWS which is faster than any other function for counting rows which is returned by the latest query, it is used same as MySQL “LASTINSERT_ID()”.
For example: if we are developing a pagination script and we want the total number of results returned by the Query, at the same case in pagination we use LIMIT for paging the record using the above function it will not return the limit results it will return the total available results.
How to use? suppose we have 20 records in our table and we are fetching only 10 records in a page, then the $total will return total 20 not 10.
$sql = “SELECT SQL_CALC_FOUND_ROWS * FROM tablename LIMIT 1, 10 “;
$results = mysql_query($sql);
$total = mysql_query(’SELECT FOUND_ROWS() as total’);
Getting first day of the month in mysql
Here is a small trick to get first day of the month
SELECT DATE_SUB(now(),INTERVAL (DAY(now())-1) DAY)
This will give you the first day of the current month. You can also get first day of the month for a given date just pass the date instead of now()
SELECT DATE_SUB('2010-08-10',INTERVAL (DAY('2010-08-10')-1) DAY)
mysql manual from command line
the mysql manual can be accessed from the command line itself as follows :
help “<search term>”;
MySQL – The GROUP_CONCAT() function
GROUP_CONCAT() function is used to concatenate column values into a single string.
Example:
Table ‘abc’ has following columns:
id customer_id
3 14
3 15
3 16
4 18
4 18
You can use this query:
SELECT id,GROUP_CONCAT(customer_id) FROM abc WHERE id = 3 GROUP BY id;
output:
id customer_id
3 14,15,16
It is useful when you want PHP array without looping inside PHP:
Convert ‘null’ to 0 in mysql
To convert a null value to zero returned by a mysql query use the function COALESCE(value,…)
Mysql Query :
SELECT COALESCE(column_that_might_be_null,0) as column_with_null_converted_to_zero FROM table_with_column_that_might_be_null;
Using conditions in mysql queries
If we want to use “if then” conditions in our code, in some cases, it can be done in mysql queries itself. For eg .
SELECT IFNULL(a,b) ) as val
In the above statement, the value of val will be equal to “a” if “a” is not null or it will be “b” if “a” is null.
another peculiar eg is “(SELECT IFNULL(special_price,p.price) ) as current_price” where the value of special_price is coming from another query in the same sql string (multiple queries).
Post A Trick !
How does "Kodetricks" work?
We at kodeplay like to share knowledge. With Kodetricks, even you can join us. All you need to do is post a programming related trick if you have one or rate a trick if you like someone else's.
