How to speed up your MySQL queries 300 times

[Baxajaun]

Hi,

here you are an article in opensource

https://opensource.com/article/17/5/speed-your-mysql-queries-300-times?sc_cid=70160000000xYIuAAM

Best regards,

[cnavarro]

Thanks

[nageswaragunupudi]

To help knowledgeable users like you FWMARIABD provides

oCn:Explain( cSql, [aParms] )

[ariston.ap]

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."

[nageswaragunupudi]

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.

[Enrico Maria Giordano]

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.

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.

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

[nageswaragunupudi]

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.

[Enrico Maria Giordano]

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.

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

[nageswaragunupudi]

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.