Solved

MySql Table Optimizing

Posted on 2011-03-24
10
370 Views
Last Modified: 2012-08-14
Hi, I have a table that has nearly 3 million rows in it.
Its on a MySql db.
When i query from it simply SELECT  * FROM TABLE
it takes about 4-5 mins for it to return the results for me.

I am using this table within another larger query, and the time to run the query has exceeded 12 hours! which i think is far to much.

I believe its to do with this table.

I have tried to Optimize the table by de fragmenting it.
i have also tried creating another table from it (which i suppose is similar to the above)
but it still takes a long time to query it.

Can anyone suggest what i can do to help speed up performance on this table?
0
Comment
Question by:Putoch
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 167 total points
ID: 35205599
- there is so much thing can be suggested but this article covers almost of everything you need. hope this article helps:

1- http://forge.mysql.com/wiki/Top10SQLPerformanceTips
2- http://www.ajaxline.com/32-tips-to-speed-up-your-mysql-queries
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35205626
more suggestion:
- try to use EXPLAIN query execution plan with your sql tool. this is to check whether your query uses INDEXES or not:

EXPLAIN EXTENDED
SELECT fieldname, fieldname
FROM tablename
WHERE fieldname = xxx
ORDER BY fieldname
LIMIT 1
\G;

- the '\G' will format the output result into a readable format.
- optimize your SQL based on the EXPLAIN result
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 166 total points
ID: 35206298
>>Zones: MySQL Server, MS SQL Server<<
This has nothing to do with SQL Server.  Please request the MS SQL Server zone be removed.
0
 

Author Comment

by:Putoch
ID: 35206974
The reason i requested both zones is because i believed the theory behind any optimization of a table would be equal in either Mysql or MSSQL, the command lines may not be exactly the same , but the logic would.  And having both zones included meant i would have a better response in-case no one responded on either zone.

Please let me know if you have an issue with this acperkins
0
 

Author Comment

by:Putoch
ID: 35206985
Hi OP_Zaharin,
thanks for those links.

I have checked what i could on the table format.
I defragmented the table and then decided that instead of running the whole script (which is made up of 3 queries)
That i would Create 3 tables to hold each of the queries, and see how that works.

I'll let you know the progress.
thank you for the advice
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:Putoch
ID: 35207210
Hi All, i'm hoping someone can advice me on the EXPLAIN out put, i'm trying to read this to see what it means. If anyone could clarify it would be very much appreciated.
thanks
elaine

Explain-code.csv
0
 
LVL 8

Assisted Solution

by:wolfgang_93
wolfgang_93 earned 167 total points
ID: 35210591
Are you actually trying to fetch all 3 million+ records from the table,
or do you have a clause limiting the number?

Some factors that can cause a fetch of 3 million+ records to take
a few minutes:
-  Fetching over a network (e.g. executing the command on a
   client which is fetching that number of records from a database
   server)
-  If the table involved is a MyISAM table, there is a lot of disk
   i/o. If the table were InnoDB instead, then most pages could be
   fetched from RAM where RAM is 1000 times faster than disk.

Try fetching a smaller number of records, say 100 records, like this:
   select * from table limit 100 offset 0

Also you mentioned that this is part of a larger query?

What do you mean by that? If you are doing a join, I can see if taking
hours to run if it involves a huge table and you do not have any
indexes on the fields involved in the join.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35212633
Hi Putoch,
- a quick explanation on the explain plan column:
http://mysql-tools.com/en/optimizing-tuning-queries/33-query-execution-plan.html

from the explain plan, the column that you might want to look into is the "type, possible_keys, key and rows"
- "type" column is the join/method use to match records from many tables. it will tell you whether it does a full table scan or not.
- "possible_keys" column indicates which indexes MySQL can choose from use to find the rows
- "key" column indicates the index that MySQL actually use. if its NULL meaning its not using any Index
- "rows" column display the number of records fetch when running each of the query step and this can tell you does your query step fetch correctly the amount of records as it should or not

- in all, EXPLAIN helps you to identify if your query is using the right approach in querying the tables involves and whether is uses the right Index or a full tablescan which usually the reason why a sql query go slow...
0
 

Author Closing Comment

by:Putoch
ID: 35392178
Thanks Guys for your help on this. I was able to understand the Explain plan in Mysql, and got some good pointers on how to optimize the db and the queries i was running, unfortunately the query was still running for hours when i tried to make some changes. thanks for all your help. Putoch
0
 
LVL 8

Expert Comment

by:wolfgang_93
ID: 35394549
Sorry to hear that you still have an issue.

Going back to your original posting, I see you stated "I am using this table within another larger query".

I can't help wondering if you are talking about a JOIN for which we could have given you
more explicit help if we had an opportunity to see an example of the full query.

A big query involving a join that runs for hours can be made to run in seconds simply by making
sure that the fields involved in the join are indexed. Fields involved in sorting, grouping, and in
major selection should also be indexed for better performance.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now