CODEWITHSUNDEEP
phpmysqlsql
user2710234
user2710234

Reputation: 3225

how to make mysql queries in php run quicker

i am running queries on a table that has thousands of rows:

$sql="select * from call_history where extension_number = '0536*002' and flow = 'in' and DATE(initiated) = '".date("Y-m-d")."' ";

and its taking forever to return results.

The SQL itself is

 select * 
   from call_history 
  where extension_number = '0536*002'
    and flow = 'in' 
    and DATE(initiated) = 'dateFromYourPHPcode'

is there any way to make it run faster? should i put the where DATE(initiated) = '".date("Y-m-d")."' before the extension_number where clause?

or should i select all rows where DATE(initiated) = '".date("Y-m-d")."' and put that in a while loop then run all my other queries (where extension_number = ...) whthin the while loop?

Upvotes: 0

Views: 2391

Answers (3)

O. Jones
O. Jones

Reputation: 108641

The order of clauses under WHERE is irrelevant to optimization.

Pro-tip, also suggested by somebody else: Never use SELECT * in a query in a program unless you have a good reason to do so. "I don't feel like writing out the names of the columns I need" isn't a good reason. Always enumerate the columns you need. MySQL and other database systems can often optimize things in surprising ways when the list of data columns you need is available.

Your query contains this selection criterion.

AND DATE(initiated) = 'dateFromYourPHPcode'

Notice that this search criterion takes the form 

FUNCTION(column) = value

This form of search defeats the use of any index on that column. Your initiated column has a TIMESTAMP data type. Try this instead:

 AND initiated >= 'dateFromYourPHPcode'
 AND initiated <  'dateFromYourPHPcode' + INTERVAL 1 DAY

This will find all the initiated items in the particular day. And, because it doesn't use a function on the column value it can use an index range scan to do that, which performs well. It may, or may not, also help without an index. It's worth a try.

I suspect your ideal index for this particular search would created by

 ALTER TABLE call_history 
   ADD INDEX flowExtInit (flow, extension_number, initiated)

You should ask the administrator of the database to add this index if your query needs good performance.

Upvotes: 1

Serhat Akay
Serhat Akay

Reputation: 534

You should add index to your table. This way MySql will fetch faster. I have not tested but command should be like this:

ALTER TABLE `call_history ` ADD INDEX `callhistory` (`extension_number`,`flow`,`extension_number`,`DATE(initiated)`);

Upvotes: 0

Pupil
Pupil

Reputation: 23948

Here are some suggestions:

1) Replace SELECT * by the only fields you want.

2) Add indexing on the table fields you want as output.

3) Avoid running queries in loops. This causes multiple requests to SQL server.

4) Fetch all the data at once.

5) Apply LIMIT tag as and when required. Don't select all the records.

6) Fire two different queries: one for counting total number of records and other for fetching number of records per page (e.g. 10, 20, 50, etc...)

7) If applicable, create Database Views and get data from them instead of tables. Thanks

Upvotes: 1

Related Questions