Sorting characters and numbers in mySQL
So I have a table with a column in this form
| someColumn |
|---|
| 1 |
| A |
| 2 |
| C |
| 3 |
| 10 |
When you ORDER BY someColumn you get result in this order 1,10,2,3,A,C
as you can see the number sequence is all messed up, 10 doesn't go after 1
to fix that you can do ORDER BY (someColum + 0) to convert the sequence to integers
but that leaves the characters out of order now. The characters get sorted by creation date
this is a very inconvenient problem.
To keep this short this is the solution
SELECT someColum as sort, someColumn, otherColums... WHERE someOther = 'somevalue' ORDER BY sort ASC, (someColumn +0)ASC
by Adding a sort column using 'someColum' we can now perform two sorting operations on the same Column first by chars then by converting 'someColum' to integerss. This will return an output like this 1,2,3,10,A,C. If you swap the ' ORDER BY sort ASC, (someColumn +0) ASC' to' ORDER BY (someColumn +0) ASC ,sort ASC', now you get the Characters first A,C,1,2,3,10. You can also change the order to Descending by using DESC.
Delete expired entries in MYSQL
For those who want to delete items in a MySQL database based on a date field the process is very easy.
First of all you need a DATE field, for example a coupon expiration date. the data is saved in the following format: YYYY-MM-DD
Then all you need to delete older items is this:
DELETE FROM coupons WHERE expiration < '2010-12-03'
make sure that your date is set between single quotes ( ' ) if you don't then MySql will treat this as an arithmetic operation 2010 - 12 - 03 = 1995
you can use the php function date to generate a time stamp for today
$today = date("Y-m-d"); // will output something like 2010-12-03
or for yesterday you could use something like
$yestday = date("Y").'-'.date("m").'-'. date("d")-1;
you can subtract an integer to either thedate("Y") or date("m") to go back on years or months
Efficient use of COUNT()
The other day I was trying to figure out how many records there were in my DB I found in a website that I needed to use COUNT(*). The example given went something like this:
SELECT COUNT(*) FROM 'table'
and while this worked as advertised, I got a very inconvenient side effect. The query was consuming more than 40% processor resources and it was taking longer than 18s to complete! now that is a lot of time to count records! even if we have upwards of 50,000.
The problem laid on the '*', this query was loading every single row and every single column in that row. even thought it was only counting the rows it was consuming ridiculous amounts of resources. Of course any experienced SQL programmer would have noticed the problem, but being the first time I used COUNT() it completely bypassed me. I never found anything on that site or others that hinted at this problems.
The solution to the problem was extremely simple thought. I just changed the '*' for the smallest field on the table, 'id' in my case,
and now a 40%-CPU-usage-18sec-completion time has been reduced to 0.001 seconds. Here is what the new query looks like
SELECT COUNT(id) FROM table
What I took from this experience is that we always should look at our queries execution time after they are deployed. it is not enough to look at them during development since, most times, the test data is far from real world.