-
Recently Browsing 0 members
No registered users viewing this page.
-
Similar Content
-
By 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 ).
-
By argumentum
I was thinking but I don't have the experience, so you may have the experience.
I was thinking to chop a DB with 100.000 ( 20 columns ) in 10.000 DB chunks ( 10 DBs ) and query concurrently all of them to speed up a search, then add the results. Is that a sound idea ? Or will I run in trouble down the road. Should a DB better be keep in one piece ?
The DB is now in MySQL. I wanna do all this chopping and use SQLite.
Thanks
[solved]
-
-
By rynow
romaSQL
This autoIt UDF is built on the concept of Laravel Query & doctrine.
RomaSQL provides a new, comfortable and easy to use way for SQL-queries in autoIt.
Most of the common SQL-queries are supported already and more are coming soon.
All of your support is much appreciated.
Connections
For the connection the object ADODB is used. Therefore the connection string is based on ODBC.
You can also use OLEDB connection strings or other database connections.
In order for this to work your add-ons have to be installed in the function: __4ern_SQL_Connection.
I’d be very glad if you shared your modifications with me.
Currently supported connections
- MySQL (odbc)
- Microsoft SQL Server (odbc)
- SQLite (odbc)
- Microsoft Access (odbc)
Command reference
$SQL_connect; establishing connection $SQL_returnType; return a Array or Dictionary ('oDict') Object (Default = Array) $SQL_setDefaultTable; Default Tablename $SQL_setDefaultKey; Default Colmn Key (Default = id) $SQL_debug; if True, show SQL Statment in Console $SQL_get $SQL_update $SQL_delete $SQL_insertInto $SQL_take $SQL_limit $SQL_table $SQL_select $SQL_distinct $SQL_where $SQL_orWhere $SQL_whereBetween $SQL_whereNotBetween $SQL_whereIn $SQL_whereNotIn $SQL_whereNull $SQL_whereNotNull $SQL_having $SQL_orHaving $SQL_havingBetween $SQL_havingNotBetween $SQL_havingIn $SQL_havingNotIn $SQL_havingNull $SQL_havingNotNull $SQL_groupBy $SQL_orderBy
Examples
establishing connection
;-----/ ; SQLite Connection ;-----/ $SQL_setDatabase('sqlite') $SQL_connect('C:\project.db') ;-----/ ; Access Connection ; Database, User, Password ;-----/ $SQL_setDatabase('access') $SQL_connect('C:\project.mdb') ;or as Admin $SQL_connect('C:\project.mdb', '4ern', 'root') ;-----/ ; SQLServer Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('sqlserver') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'SQL Server') ;-----/ ; MySQL Connection ; Database, User, Password, Server, Driver ;-----/ $SQL_setDatabase('mysql') $SQL_connect('myDB', '4ern', 'root', 'localhost') ;or with Driver $SQL_connect('myDB', '4ern', 'root', 'localhost', 'MySQL ODBC 5.2 UNICODE Driver')
simple SQL query
$SQL_table('albums') $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif
Select
$SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song') ;or pass to an Array Local $aSelect = ['id', 'Name', 'Artist', 'Song'] $SQL_select($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif where
$SQL_table('albums') $SQL_select('id', 'Name', 'Artist', 'Song', 'Votes') $SQL_where('Artist', 'adele') $SQL_where('Votes', '>=' ,'9') $SQL_orWhere('Artist', '=' ,'Rag'n'Bone Man') ;or pass to an 2dArray Local $aSelect = [['Artist','adele'],['Votes', '>=' ,'9']] $SQL_where($aSelect) $aRet = $SQL_get() if IsArray($aRet ) then _ArrayDisplay($aRet ) else ConsoleWrite('Keine Ergebnisse' & @LF) endif
If you need more examples, then tell me exactly what you need.
I hope you like my UDF and find some use for it.
---
->DONWLOAD romaSQL
-
By macran
I use mysql.au3 to connect mysql database . If restart the server ,the client how to reconnect mysql?
The function prototype is
_MySQL_Options($MySQL_ptr, $option, $arg = 0, $type = "ptr")
so,
_MySQL_Options($MysqlConn,$MYSQL_OPT_RECONNECT, ?, ?)
pls help me,thanks!
-
Recommended Posts