speed up order by int DESC, text ASC;

[argumentum]

$sQueryUpdateTime = "select intUpdateTime from tblStudies " . $where . " ORDER BY intUpdateTime DESC limit 1";
    $rs = mysqli_query($conn, $sQueryUpdateTime);
    $row = mysqli_fetch_assoc($rs);

the above used to take 300+ ms. to query. Then I set it as index and takes 30 ms. Cool.

$sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC limit $offset,$rows";
// takes 30 ms. on the indexed int

$sQuery = "select * from tblStudies " . $where . " ORDER BY StudyDate DESC , PatientName ASC limit $offset,$rows";
// takes 300 ms. due to "PatientName" been a text field, even as I did index it

So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.

..tho, I find that any 2nd argument in the ORDER BY is just slow.

Is there a way to query this in a way to have a faster result back ?

Thanks

PS: added ADD INDEX `StudyDate_2` (`StudyDate`, `PatientBirthDate`) USING BTREE;  and searched by those two with not much speed change ( StudyDate and PatientBirthDate are integer ).

Edited March 3, 2020 by argumentum

[argumentum]

so, brain storming, I figure that the way, or only way with my experience, is to make a new column named "order_Dt_Na" and to the date add "001 to 999", where I'd query all names for that date ( that should be under 100, so no big deal ), sort and update the order_Dt_Na column. Every search will be ORDER BY StudyDate DESC, PatientName ASC , so might as well simplify by just ORDER BY order_Dt_Na .

If you have a better idea, or just know better, let me know.

[jchd]

12 hours ago, argumentum said:

So my observation is that "PatientName" takes a long time to sort, even tho "$rows = 20". Sorting text in 20 rows should be fast.

Not so. Whatever engine you use, the query planner must first apply your where clause to select the resulting subset. Then it must sort it as a whole (probably more than 20 rows); this can be helped by an index on (StudyDate, PatientName). Only then it has to honor the offset=N and skip the first N rows, finally output while counting up to 20.

[Andreik]

If your only problem is the sorting being too slow then you should consider getting your data as it is and sort it by yourself. This can be a faster sort than how your db engine can perform.

[jchd]

I doubt it since AutoIt is interpreted while any SQL engine is fine-tuned C-like compiled code.

EDIT: BTW doing the sort at application level means doing exactly the same thing as how SQL works as I described in the post above.

You can see that by considering the following job to be done by hand (you're a processor now!):

You're given a deck of 52 regular cards in random order.
Cards fall in 4 colours: Spades, Hearts, Diamonds & Clubs.
Each color is associated to a multiplier: Clubs * 1, Diamonds * 2, Hearts * 3 and Spades * 4.
Say cards are valued like this:  numbered cards = their face value (1=1, ..., 7=7, 10=10), then Jack = 20, Queen = 30, King = 40, each multiplied by the coefficient of the card's colour.
Cards have a name: 1..10 = ace, two, three, ..., ten, jack, queen, king.
You must extract cards in Hearts or Clubs, have at least one 'n' in their name, sort them by name in ascending order and value by descending order, ignoring the first 4 and outputing the next 6.

It's easy to see by oneself that you just can't start sorting the cards and ignoring a number of them until you're done selecting which one is allowed. Yes you partly can but then you must interleave the selection (where clause) and the sort, something very inefficient when coded. In any case you can't starting ignoring cards until the selection/sort steps are done.

Now consider a table of 52 million rows and you give SQL big credit for doing the job for you. Of course creating the most efficient index for a given query greatly helps the query planner.

Edited March 4, 2020 by jchd

[Andreik]

After so many years as DBA and database developer I think I can be a good processor.

Who said anything about AutoIt? I mean of course AutoIt arrays won't be faster than database engines but sorting algorithms are pretty easy to be implemented in ASM. Anyway what OP asked is  regarding the two queries that sort data by one or two columns. For MS SQL Server for example this doesn't matter, a simple look at the execution plan will reveal you identical executions in the same amount of time. I don't know what database engine OP use but it's pretty obvious that something else is going on there. Maybe showing us the design of that table and your indexes will help us to give you better advices.

Edited March 4, 2020 by Andreik

[jchd]

That's why I proposed a compound index on both columns.

BTW sorting at app-level is still a bad idea: you need to obtain the whole subset of rows from the where clause and if the subset is large that implies exporting the data from the server to the client before the latter can sort, ignore and count. Better make good use of the server fine-tuned code and RAM caches.

[Andreik]

That's true but he talked about 20 rows or something like that this is why I never think about at a big scale.

[jchd]

Again, the 20 rows is the consequence of the limit 20 clause. If the selected subset (after processing the where clause) is 50 million rows, you still have to get your hand on all that many rows to sort them all, then ignore the X first of that sorted resultset (offset X clause) and output the Y next (limit Y clause).

[Andreik]

I know my friend how it's work, I said that I had in my mind a small number of rows. This happens when you read the forum at 3 AM.

[argumentum]

5 hours ago, Andreik said:

For MS SQL Server for example this doesn't matter

I'm using MySQL but is the MariaDB flavor that comes with XAMPP.
After searching with this TEXT column, I change it to CHAR(60) but to no avail. It took pretty much the same time.
Then I index the heck out of it but the single indexing of both columns did not show noticeable improvement.
Then I added a 21st column, with a int(11) with the date and a counter ( YYYYMMDDnn ) and that gave me the best result as is just sorting by one int. column.

But, and this is a big but. The DB engine. I'd love for the implementation of the SQLite ( the UDF ), to be faster than the MySQL that comes with XAMPP, but if MS SQL is faster and as maintenance free as MySQL then I'd make the jump to MS SQL. Not the full fledged one but a "personal" or express version. Again, if SQLite is on par with these other bigger DB engines, SQLite is portable AND, that makes it beautiful in my eyes 😍

Anyways, there are so many flip-flops in a CPU. Meaning, they all are optimized code with years of development, my choice would be based in my experience and I have little.

So we are biased by our own experiences and abilities. Would you care to tell me what way would you go in my case ?, no triggers or other "fancy" DB use. Just INSERT, UPDATE and SELECT. What say you ?   

Edit: from https:// www.sqlite.org/whentouse.html 
SQLite is not directly comparable to client/server SQL database engines such as MySQL, Oracle, PostgreSQL, or SQL Server since SQLite is trying to solve a different problem.
SQLite does not compete with client/server databases. SQLite competes with fopen().

https://www.sqlservercentral.com/articles/single-user-performance-of-sqlite-v-sql-server is also a good read.

Edit #2: I'll import the DB to SQLite, and test it. That will decide what is actually better  

Edited March 5, 2020 by argumentum

[jchd]

6 hours ago, argumentum said:

Would you care to tell me what way would you go in my case ?, no triggers or other "fancy" DB use. Just INSERT, UPDATE and SELECT. What say you ?

Please tell what entities you're managing. From what I gather from the thread, you have:

Table tblstudies:
rowid integer (probably),
studydate char,
intUpdateTime char,
patientname char,
PatientBirthDate char,
probably more columns.

I'll try to make something up later today.

[argumentum]

intUpdateTime is a bigInt(20) to fit "20200229033627", PatientBirthDate is int(11) as "19991231" fits, ..used Integers as much as I can, else, TEXT.
And yes, I have a "RowID" as primary index, as it was an import from SQLite. And there are other tables too.

All these started with SQLite and PHP in my brainstorming/developing days back in 2018, then when I got a better working understanding moved to MySQL and PHP, but now I know that I can do it all in AutoIt and have no need for PHP. So I wanna move it all to AutoIt since my discovery of SSL for AutoIt ( that I found by chance looking into Syncthing ).
HTML is HTML, no matter what generates the HTML. Same with SQL stuff. It all comes down to responsiveness since these web sites have no more than 20 users. Not even 1 user a minute so is not a highly/heavy loaded site(s).
The site is in the customer's server, as I don't find the need to have the site on another "cloud" provider. Already have a server  
The idea of going to AutoIt/SQLite, is due to the noseyness of the "server admin." in one company, so, I'd move the "magic" site to an encrypted folder in my user account and protect my work while coding in a language I'm familiar with.

Now, back to SQL. I figure that SQLite could run in :memory: and outperform any other DB engine due to the usage I'll give it.
Same with PHP. In AutoIt it will have the pages as $sStrings, all in memory.

This setup should kick the heck out of anything else, at least in my mind. But I will only know for sure once is done and tested.

Now. I have no schooling and very low experience compared with y'all, so ...., I post to ask for advice since my trend of thought, could be basically flawed, period!, and go nowhere fast ( actually painfully slow as I code everything to no avail ) 😅

...and that is the full story   

[Andreik]

SQL Server it's pretty reliable and easy to work with. With express edition you are limited at 1 physical processor, 1 GB memory, and 10 GB storage but hey it's completely free. So if I understand well, it's for a website without too much traffic. If this is the case and you can work with these limitations you MS SQL is a powerful database engine.

[argumentum]

1 hour ago, Andreik said:

If this is the case and you can work with these limitations you MS SQL is a powerful database engine.

In my case they are not limiting, as the site will never get to 1 GB, since 10 years of data takes 100 k.
But is it's performance better than MySQL ?. MySQL need not be "installed". It can run as service but as user too.
And for SQLite, it only takes to load a DLL.

I'll post my findings once I finish coding the "MySQL 2 SQLite DB importer", and query via an API i'll put together as the DB engine*.

*I'll use the HTTP server to run the SQLite. It'll add an extra 5 ms. to the total time of the query.

[Andreik]

When it comes to databases there are two serious giants: oracle and sql server so yes it's a pretty reliable and performant database engine with a lot of features. But of course this doesn't means it would be the best fit for every project. Since yours it's quite small and will not grow very much in time sqlite might be a better choice and will give you more portability. You just need to find the balance between the features, performance and your requirements.

[argumentum]

On 3/5/2020 at 1:13 PM, argumentum said:

This setup should kick the heck out of anything else, at least in my mind. But I will only know for sure once is done and tested.

Update: Done and tested. It does not "kick the heck out of anything else".
Is about 1/3 rd slower ( ~200 ms. vs. ~300 ms. on a slow PC ). Not that bad for an all (almost all)  AutoIt solution (the HTTP is encapsulated by Apache to have SSL).

 

On an unrelated note:

I needed to return the query as JSON and the only solution I came up with is:

Local $iReturnJson = 2 ; ...this is part of a Func but this is the JSONfier loop
    Local $sJson = ""
    ; $aResult comes from _SQLite_GetTable2d()
    For $n1 = 1 To UBound($aResult) - 1
        $sJson &= '{'
        For $n2 = 0 To UBound($aResult, 2) - 1
            $sJson &= '"' & $aResult[0][$n2] & '":' & Json_Encode($aResult[$n1][$n2]) & ','
        Next
        $sJson = StringTrimRight($sJson, 1) & '},'
        If $iReturnJson > 1 Then $sJson &= @CRLF
    Next
    $sJson = StringTrimRight($sJson, ($iReturnJson > 1 ? 3 : 1))
;~  ConsoleWrite($sJson & @CRLF)
    Return $sJson

..tried to use json_group_array() but could not get it to work.
Do any of you know if our SQLite can return the data as JSON ?
Does it only work if CREATE TABLE json_tree() ?

There is no clear and concise examples of a regular SQLite db and getting the data as JSON that I can find and understand. 

[jchd]

The SQLite JSON1 extension provides functions to check, navigate, parse, extract, update Json data; but it does offers only very limited functionality to produce general Json output from the result of a query.

It isn't completely impossible to achieve but this is very cumbersome and each query would need a long and complex ad-hoc formatting.

[argumentum]

..I've found an example that, even tho not exactly as I expected, does the job.
I posted the AutoItfied code at https://www.autoitscript.com/forum/topic/201992-sqlite-returning-query-as-json/   

 

[argumentum]

On 3/7/2020 at 3:41 PM, argumentum said:

Update: Done and tested. It does not "kick the heck out of anything else".

Update #2: It does "kick the heck out of anything else".
Is twice as fast as PHP with MySQL/MariaDB. Tested on 2 PCs.
This speed is due to getting the JSON I needed straight from SQLite with the DB in :memory:.

I wanted to share this, to not leave the post with the believe that coding a whole site in AutoIt is impractical, or slower