Advice Please : Speed up this query

[DR.Wong]

Hi there everyone!

I need a little help speeding up one of my MySQL queries.

I have a table that I keep track of invoices with called "invoices".

This table has a bunch of columns but for the purposes of this post, the only important ones are "id" and "date" (id being the unique key).

I have a php page which displays a list of the invoices in my database. This database has grown to the point where I have had to add pagination and ontop of the pagination, two drop down menus to select a year to display and next to that, a month within the year.

My page is becoming really slow as I have a number of queries to figure all of this out.

The first query finds all of the years and creates an array of years without any duplicates which populates the first drop down menu.

The second query finds all of the months within the selected year and creates an array to populate the second drop down menu.

The third query finds the total number of invoices with the selected year and month in their date (with format d-m-y as char).

And a fourth query handles the pagination.

The queries are all pretty simple : select * from invoices where date='xxx'

Not much reason to post them.

Is there a quicker way of doing this? I can understand that the page would be slowing down.

Thanks a lot!

Nick

[abduraooft]

The queries are all pretty simple : select * from invoices where date='xxx' Whatever the case, select * is very inefficient, since you are fetching all the columns instead of the only required column(s).

The first query finds all of the years and creates an array of years without any duplicates which populates the first drop down menu.

The second query finds all of the months within the selected year and creates an array to populate the second drop down menu.

The third query finds the total number of invoices with the selected year and month in their date (with format d-m-y as char). Have a try after adding indexes on these columns, if not indexed yet.
And a fourth query handles the pagination. Are you using the same select * in this case too?

[MattF]

Why are you creating the year and month lists from DB queries? Surely it would make more sense to just hardcode those option lists and then check whether the DB contains any invoices for that month of the year when the form is submitted and return a no entries message if none are.

[bazz]

The third query finds the total number of invoices with the selected year and month in their date (with format d-m-y as char).


The date should be yyyy-mm-dd and the data type should be DATE. This might also help speed up your query but I reckon, seelcting only the cols you need (by getting rid of the * ) and indexing, are the more significant impropvements.

it would help us if you posted your query and your CREATE tables statements.

bazz

[DR.Wong]

@abduraooft : Yes, when I was learning MySQL (still learning) all of the tutorials I ever read used "Select * From Database", so that * appears in ALL of my select queries.

@MattF : It isn't hard coded as I want the app I'm making to expand. If someone else is to use this app their invoices won't start from the same date as mine, thus the years have to be determined from the data in the particular database. Also, the years are financial years so the script works out which financial years have come to pass. (Eg : My financial year starts in March thus my year runs from March 2009 until March 2010 etc) Everyone has different financial years depending on when their business started.

@bazz : I use d-m-Y as it is a more readable format (more generally accepted) where I come from. I am planning on converting them all into timestamps in a separate column in any case. From what I'm reading in this thread, my script is probably a mess so I'll post it once I've asked my questions and fixed it up. I wanna learn.

@everyone :

It seems I have very little idea what I'm doing with SQL.

If you would, please enlighten me on the following :

1. How should a select statement look? I take it "select * from entries where value=myvalue" is not the best route to take? I use this and then use a while statement to echo out the rows :

while ($row = mysql_fetch_assoc($result)) { //Code }

2. What do you mean by indexing?

[abduraooft]

1. How should a select statement look? I take it "select * from entries where value=myvalue" is not the best route to take? I use this and then use a while statement to echo out the rows : You might probably need something like
select distinct year_column from table_name where .....
2. What do you mean by indexing?
http://dev.mysql.com/doc/refman/5.0/en/create-index.html

[MattF]

If, for example, you only need the date and id column values from a table, only select the date and id values:


SELECT id, date FROM..... etc.


Be implicit rather than selecting everything with *. Only use the asterisk if you specifically need all column values returning from a table.

[bazz]

Hi,

I use d-m-Y as it is a more readable format (more generally accepted) where I come from.


yyyy-mm-dd in a column formated as a DATE data type, is more readable by MySql. That is the best format for an efficient comparison of dates. Then you can manipulate it to read it in whatever date format you want.

bazz

[DR.Wong]

@abduraooft and @MattF : Oh I see. The * is right for me in most situations then, but if I'm only finding the years then it is uneccessary to select all the other info too. Thanks a lot!

@bazz : I understand, so I could just use a function to transform the MySQL friendly value into my required format. What is the advantage of using that date format and the date type in the database? I usually find all the entries for a specific month with a select similar to :

SELECT * from my_table WHERE date like "%05-2009"

(As an example) This returns all of the values from 05-2009 if I'm looking for a list from a specific month. (Given that my current format is 12-05-2009 or dd-mm-yyyy)

[abduraooft]

2. What do you mean by indexing?
I think I should have given resources like http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html
http://www.sitepoint.com/article/optimizing-mysql-application/

[bazz]

@bazz : I understand, so I could just use a function to transform the MySQL friendly value into my required format.

I would suggest you consider it to be the correct date format and not, mysql friendly. It is the date format for MySQL


What is the advantage of using that date format and the date type in the database?


MySql is the database language and it will be a more efficient system, if you use the correect format for each specific type of data.


I usually find all the entries for a specific month with a select similar to :

SELECT * from my_table WHERE date like "%05-2009"

(As an example) This returns all of the values from 05-2009 if I'm looking for a list from a specific month. (Given that my current format is 12-05-2009 or dd-mm-yyyy)

If you want your db to be scaleable and, therefore, capable of doing things in the future that you haven't even thought of yet, you need to make it scalable. This means you must use the correct formats for each data type. If you choose not to, then in the future you may have to re-build the whole db so it can work, be manageable and, retain referential integrity.

Put more simply: a db needs to: -

1. be normalised to at least 3rd normal form (look up 'database normalisation')
2. have good referential integrity (look up referential integrity or relational integrity)
3. be scalable
4. utilise foreign keys where appropriate. (these help with RI).

A good tips would be:
the data in your db should bear no relatioship to the queries you want to perform. the data should simply be normalised. then your query can find what it needs whilst (simultaneously), you can be confident that the db is set up to be as efficient as possible.

As you have it now, your data is set up to enable/assist one type of query. If you ever need to use the date function in other ways, you will either need to put the date in twice in two different formats (not a normalised db) or you may have inefficient queries.

hth

bazz

[bazz]

following aduraooft's idea, I shall post a link :)

http://dev.mysql.com/doc/refman/5.0/en/datetime.html

bazz

[DR.Wong]

Thanks a lot guys! I'm going to try and incorporate your wisdom.

[Old Pedant]

(1) It doesn't really matter what the INTERNAL format (in MySQL) is of a DATETIME value. When you grab that value via PHP, you can and should use PHP functions to transform it into a human readable form. So go with the flow. Use *proper* MySQL datetime values.

(2) To extract month and year from a date, you use...what else...the MONTH() and YEAR() functions:

SELECT DISTINCT Year(`date`) AS theYear FROM yourtable ORDER BY theYear

(Notice that I disagree with abudraooft...you should clearly *NOT* have a separate "yearcolumn" in your table.)
You would replace your
SELECT * from my_table WHERE date like "%05-2009"

With maybe:

SELECT ... FROM yourtable
WHERE Year(`date`) = 2009 AND Month(`date`) = 5

By the way, I put `date` in the tick marks because date is a builtin function in MySQL. You should avoid such names. Or, if you use them, put the `...` tick marks around them in queries.