How to speed up your MySQL queries 300 times

How to speed up your MySQL queries 300 times

Postby Baxajaun » Tue May 09, 2017 7:54 am

User avatar
Baxajaun
 
Posts: 968
Joined: Wed Oct 19, 2005 2:17 pm
Location: Gatika. Bizkaia

Re: How to speed up your MySQL queries 300 times

Postby cnavarro » Tue May 09, 2017 4:10 pm

Thanks
Cristobal Navarro
Hay dos tipos de personas: las que te hacen perder el tiempo y las que te hacen perder la noción del tiempo
El secreto de la felicidad no está en hacer lo que te gusta, sino en que te guste lo que haces
User avatar
cnavarro
 
Posts: 6541
Joined: Wed Feb 15, 2012 8:25 pm
Location: España

Re: How to speed up your MySQL queries 300 times

Postby nageswaragunupudi » Wed May 10, 2017 5:23 am

To help knowledgeable users like you FWMARIABD provides

oCn:Explain( cSql, [aParms] )
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10632
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: How to speed up your MySQL queries 300 times

Postby ariston.ap » Tue May 16, 2017 11:59 pm

This comment from a reader on that post called my attention:

"Totally misleading title. It should be named "MySQL for beginners", because this is really database 101 for DBA's. What is less known is that most SQL functions totally ignore indexes and incite a full table scan. Or that adding indexes like pepper and salt can actually SLOW down a database. Or SQL that totally works for one RDBMS will not necessarily perform very well on another. Next time do something fancy with temp tables or stuff and earn that title. Why this got in the top 5 is beyond me."
[~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~]
FWH1206 / XHB121 / BCC582 / RESOURCE WORKSHOP / XDEVSTUDIO
Please visit: arsoft-ap.com
[~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~]
User avatar
ariston.ap
 
Posts: 11
Joined: Mon Jun 29, 2015 8:16 pm
Location: Macapá - AP - Brazil

Re: How to speed up your MySQL queries 300 times

Postby nageswaragunupudi » Wed May 17, 2017 12:49 am

Many of us here like me are beginners. This article is good for us. There is something to be learnt from that comment too, even if it sounds arrogant. There are other comments also that are informative.

Many of us like me are XBase programmers trying to migrate to RDBMs. May be in my view the only purpose of index is to SET ORDER and navigate in that order. I may not even be knowing that indexes are used by the RDD to optimize filters. Even if I know that I may not be knowing how to build a filter expression (or index expression) to take advantage of the optimization.

It is from this stage many of us need to learn. I will again learn my mistakes after I start adding indexes like salt and pepper.

I consider that the above article is useful because:

1) I now learn that there is a need to optimize queries. This is the most important lesson. Till yesterday I was writing queries the way I liked and blaming other factors or libraries for slow performance.
2) I know that indexes play a role in optimization. This brings me to the point that optimization begins with database design.
3) I know how to examine my SQL statements. Good for many of us who did not know the existence of "EXPLAIN" and how to use it.

This is a good beginning.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10632
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: How to speed up your MySQL queries 300 times

Postby Enrico Maria Giordano » Wed May 17, 2017 2:56 pm

ariston.ap wrote:What is less known is that most SQL functions totally ignore indexes and incite a full table scan.


Any decent SQL engine use indexes for query optimization. This is a well known fact since the 70's.

ariston.ap wrote:Or that adding indexes like pepper and salt can actually SLOW down a database.


This is true. Any new indexe requires additional engine's work.

ariston.ap wrote:Or SQL that totally works for one RDBMS will not necessarily perform very well on another.


True. Unfortunately SQL is not a standard language so any engine uses its own dialect. This makes difficult but not impossible to write portable SQL queries.

EMG
User avatar
Enrico Maria Giordano
 
Posts: 8713
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: How to speed up your MySQL queries 300 times

Postby nageswaragunupudi » Wed May 17, 2017 3:48 pm

This is just an open academic discussion
What is less known is that most SQL functions totally ignore indexes and incite a full table scan.


This is a fact.

There are situations where the optimizer decides table scan is faster than index scan. Index scan involves two steps. Scan the index and then pick the rows from the table.

Example that is often quoted on this subject: If male employees are above 90% then for choosing all male employees direct table scan is faster and for choosing female employees, index scan is faster.

Rule based optimization which was prevalent long time back always used indexes. Oracle moved from RBO to Cost Based Optimization long time back (I think around 20 years back). Even MySql uses cost based optimization. CBO may not always use indexes.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10632
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: How to speed up your MySQL queries 300 times

Postby Enrico Maria Giordano » Wed May 17, 2017 4:37 pm

nageswaragunupudi wrote:
What is less known is that most SQL functions totally ignore indexes and incite a full table scan.


This is a fact.


No, this is simply not true.

nageswaragunupudi wrote:There are situations where the optimizer decides table scan is faster than index scan.


That is very different from the quoted statement, isn't it?

EMG
User avatar
Enrico Maria Giordano
 
Posts: 8713
Joined: Thu Oct 06, 2005 8:17 pm
Location: Roma - Italia

Re: How to speed up your MySQL queries 300 times

Postby nageswaragunupudi » Wed May 17, 2017 5:16 pm

No, this is simply not true.


With all my personal very high regards to you, I humbly but firmly disagree.

My intention is not to argue but to let the users be aware of the subject.
The gentleman who posted the comment in question is a knowledgeable person on the subject.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10632
Joined: Sun Nov 19, 2006 5:22 am
Location: India


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 114 guests