how to make mysql queries in php run quicker

Question

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?

Databases 101: Analyse the SQL query by using EXPLAIN, and learn to index your data tables — Mark Baker
– Mark Baker, Commented Sep 30, 2013 at 11:03

Pupil · Accepted Answer · 2013-09-30 11:12:41Z

1

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

answered Sep 30, 2013 at 11:12

Pupil

24k6 gold badges50 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Dvorak Over a year ago

what are indexes for on columns that you don't select by? Indexes should be on columns you sort by or filter with.

O. Jones · Accepted Answer · 2013-09-30 11:38:50Z

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.

Serhat Akay · Accepted Answer · 2013-09-30 11:13:55Z

0

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)`);

answered Sep 30, 2013 at 11:13

Serhat Akay

5343 silver badges10 bronze badges

Collectives™ on Stack Overflow

how to make mysql queries in php run quicker

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related