Drastically Improve Sql Query Performance

1. Windows 2003 Server
   
2. MySQL Server
   
3. My records are around 3 million.
   
4. My Query that is used in phpr list page:
   SELECT
   
   id,
   
   cid,
   
   ip,
   
   start,
   
   end,
   
   cost,
   
   prefix,
   
   route,
   
   z_year,
   
   z_month
   
   FROM cust_calls
   
   WHERE
   
   cid = 123 AND c_type = 0 AND z_year = YEAR(NOW()) AND z_month = MONTH(NOW())
   ORDER BY id ;
   Here's what happened:
   Using my previous practice, i created individual indexes in mySQL for my cust_calls table,
   
   which cover all the query condition fields, i.e:
   
   id, c_type, z_year, z_month.
   Total 4 indexes created.
   I thought as long as the required indexes created,
   
   whether i created separately or combined them, it should be fine.
   
   I was wrong, or understood that wrongly.
   Frustrated with the slow speed, i just created a single index with all the following fields checked:
   
   id, c_type, z_year, z_month.
   The results:
   It only took less than 0.16 sec to get all the 36K rows!
   
   It took 7 sec for the same using old index method.
   However, using Explain command to see how efficient are the 2 indexes
   
   before and after, the old method showed total 4K rows are required to process,
   
   while new method showed 56K rows. I am not sure why is it so, but apparently
   
   creating a combination of fields for an index for targeted slow SQL query,
   
   can drastically improve the speed and worth the effort.
   So next time, if you have any page that loads slowly, don't just create the index,
   
   instead create a SINGLE index that contains ALL the fields in your SQL query condition.
   It saved my day, for people who are starting to look into optimizing
   
   SQL Speed, i hope this will help.
   

Thanks for the info, I found this really interesting.

Just can you clarify, how to create a single index on multiple fields?
Cheers

Paul.

Hi Paul,
During creating of index using a GUI tool, you can select one or multiple fields.
I suggest you get a free MYSQL GUI tool, SQLyog at https://code.google.com/p/sqlyog/downloads/list

, and use it to manage your MYSQL database.
Cheers

ACP

Cheers.

I get what you mean now.

I used PHPMyAdmin, selected a couple of fields which have ID numbers in them, then I clicked on Index, and did it that way.
It is quick if I run SELECT * from jobs, but when I access it using a webpage through a php site, it takes about 5 seconds to display.

I think I have to try and get it away from a windows server and run it in WAMP somehow
Cheers

Paul.

Just to add to this conversation, here is a VERY useful explanation on how to gauge the efficiency of your indexes in MySQL:
http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm
Well worth the read.

Thanks FunkDaddy, it is very good article. Especially, Mysql use leftmost Prefixing for Index, means creating a joint

fields index can be very useful and yet due to Leftmost Prefixing nature of MySQL, it may not be used at

all to certain SQL Select. So it is important to know if you index is used at all using Explain command.
" ALTER TABLE employee ADD INDEX(surname,firstname);
is used for a queries such as
EXPLAIN SELECT overtime_rate FROM employee WHERE surname='Madida';
as well as
EXPLAIN SELECT overtime_rate FROM employee WHERE surname='Madida' and firstname="Mpho";
However, the query
EXPLAIN SELECT overtime_rate FROM employee WHERE firstname='Mpho';
does not use an index, as firstname is not available from the left of the index,

If you needed this kind of query, you would have to add a separate index on firstname. ";
AND it is good idea to create index with length restrictions as often we don't need

the full length for searching.
"ALTER TABLE employee ADD INDEX(surname(20),firstname(20));"
Details are in the link provided by FunkDaddy.